|
View previous topic :: View next topic
|
| Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
Hi,
My reqt is as follows:
We need to extract row from TABLEA if the Effective date <> '9999-12-31' and State ID (got from Table B based on Acct No) and Type No (extracted from Table C based on Acct ID, Eff date and Typ cod = CCME.
Conditon - If the State ID extracted from TableB is 'CA', default the Type No to '5' else use the extracted Type Val from TABLEC.
The below query is working fine for the above mentioned reqt.
Additional reqt - If we are NOT getting any row from TABLEC, default Type No to '1'. Can anyone please help me in achieving that in this query.
We can do that in multiple queries but modifying the below query will be great.
| Code: |
SELECT * FROM TABLEA A
WHERE EFF_DT <> '9999-12-31'
AND A.ST_ID = (SELECT ST_ID FROM TABLEB WHERE ACCT_ID = 100)
AND A.TYP_NO = CASE A.ST_ID
WHEN 'CA' THEN 5
ELSE
(SELECT TYPE_VAL FROM TABLEC
WHERE PLCY_EFF_DT = '2009-08-01'
AND ACCT = 100
AND TYPE_CD = 'CCME')
END; |
Thanks
Vinu |
|
| Back to top |
|
 |
bipinpeter
Active User
.jpg)
Joined: 18 Jun 2007
Posts: 213
Location: Cochin/Kerala/India
|
|
|
|
Hi Vinu,
Please try the below code.I dont have mainframe access so i didnt tested this code,
| Code: |
SELECT A.X,A.Y.A.Z FROM
TABLEA A
INNER JOIN TABLEB B
ON A.EFF_DT <> '9999-12-31' AND
A.ST_ID = B.ST_ID AND
B.ACCT_ID = 100
INNER JOIN TABLE C
ON (A.TYP_NO = 5 AND
A.ST_ID = 'CA') OR
(A.TYP_NO = COALESCE(c.TYPE_VAL,1) AND
C.PLCY_EFF_DT = '2009-08-01' AND
C.ACCT = 100 AND
C.TYPE_CD = 'CCME') ;;;
|
|
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| We can do that in multiple queries but modifying the below query will be great. |
If you insist on using a single query, strongly suggest you test the query with tables that are full volume. EXPLAIN can tell some things, but there is nothing quite like a full volume run to demonstrate a multi-hour query that run sub-second in simple testing. . .
Many queries run just fine when tables have only 10s or 100s of rows, but when the multi-million rows (or even the hundred-thousand rows) are processed, the performance is completely unacceptable. |
|
| Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
Thanks Bipin and D.sch.
Initially I thought to run the query with some sample rows. As per your suggestion, will run this query with table with full volume
Thanks
Vinu |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
this is one way :
| Code: |
SELECT * FROM TABLEA A
WHERE EFF_DT <> '9999-12-31'
AND A.ST_ID = (SELECT ST_ID FROM TABLEB WHERE ACCT_ID = 100)
AND A.TYP_NO = coalesce(
(select 5 from sysibm.sysdummy1 where a.st_id = 'CA')
,(SELECT TYPE_VAL FROM TABLEC WHERE PLCY_EFF_DT = '2009-08-01'
AND ACCT = 100
AND TYPE_CD = 'CCME')
,1) |
I doubt that bipinpeter solution is correct, because it will create a cartesian product with tableC for all rows of tableA where A.TYP_NO = 5 AND A.ST_ID = 'CA' |
|
| Back to top |
|
|
|
|
