Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

CASE condition logic

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Tue Jul 13, 2010 9:46 pm    Post subject: CASE condition logic
Reply with quote

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
View user's profile Send private message

bipinpeter

Active User


Joined: 18 Jun 2007
Posts: 210
Location: Cochin/Kerala/India

PostPosted: Wed Jul 14, 2010 9:20 am    Post subject: Reply to: CASE condition logic
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 14, 2010 9:30 am    Post subject:
Reply with quote

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
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 151
Location: India

PostPosted: Wed Jul 14, 2010 8:10 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jul 15, 2010 4:47 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Need help on If condition continuatio... ashok_uddaraju CLIST & REXX 4 Fri May 19, 2017 8:55 am
No new posts Need help in building a logic Benchwarmer All Other Mainframe Topics 4 Wed Feb 22, 2017 2:49 am
No new posts Joinkeys with condition scdinesh DFSORT/ICETOOL 10 Tue Feb 14, 2017 12:20 am
No new posts VALIDATE NULL VALUE IN DB2 CASE STATE... useit DB2 5 Thu Feb 09, 2017 4:34 pm
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us