Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Problem in SQL query : CASE expression

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

New User


Joined: 17 Jun 2009
Posts: 6
Location: Hyderabad

PostPosted: Fri Aug 07, 2009 10:30 am    Post subject: Problem in SQL query : CASE expression
Reply with quote

Dear All,

I'm facing problem while writing a cursor for a stored procedure.
The query which i given is only part of query and facing problem in the below scenario.
The requirement is like this:

Code:
SELECT T1.C1
      ,CASE WHEN T1.C1 = 'ACTIVE'
       THEN 'ACTIVE'
       ELSE 'INACTIVE'
       END AS STATUS_FINAL
FROM (SELECT CASE WHEN
                      ( SELECT 'Y'
                          FROM ABCD.TABLE2 T2
                         WHERE T2.COL1 = 'XXXX'
                        AND ..OTHER CONDITIONS ) = 'Y'
             THEN 'ACTIVE'
             ELSE 'INACTIVE'
             END AS C1
       FROM SYSIBM.SYSDUMMY1
       FETCH FIRST 1 ROW ONLY) T1
WITH UR;
Code'd


The above query is working fine.
when i'm selecting 2 more columns for TABLE2
then i'm unable to get check the same condition......as given below.

SELECT T1.C1
,T2.C2
,T2.C3

,CASE WHEN T1.C1 = 'ACTIVE'
THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS STATUS_FINAL
FROM (SELECT CASE WHEN
( SELECT 'Y'
,T2.C2
,T2.C3

FROM ABCD.TABLE2 T2
WHERE T2.COL1 = 'XXXX'
AND ..OTHER CONDITIONS ) = 'Y'
THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS C1
FROM SYSIBM.SYSDUMMY1
FETCH FIRST 1 ROW ONLY) T1
WITH UR;

If the sqlcode is 0 then C2 & C3 should have actual value
ELSE if 100, then column C2 & C3 should be spaces.


Your help is much appreciated.

Regards
Rindia
Back to top
View user's profile Send private message

Rindia

New User


Joined: 17 Jun 2009
Posts: 6
Location: Hyderabad

PostPosted: Fri Aug 07, 2009 10:55 am    Post subject:
Reply with quote

In other conditions ORDER BY clause exits.
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Fri Aug 07, 2009 12:14 pm    Post subject:
Reply with quote

SELECT T1.C1
,T2.C2
,T2.C3
,CASE WHEN T1.C1 = 'ACTIVE'
THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS STATUS_FINAL
FROM (SELECT CASE WHEN
( SELECT 'Y'
,T2.C2
,T2.C3
FROM ABCD.TABLE2 T2
WHERE T2.COL1 = 'XXXX'
AND ..OTHER CONDITIONS ) = 'Y'
THEN 'ACTIVE'
ELSE 'INACTIVE'
END AS C1
FROM SYSIBM.SYSDUMMY1
FETCH FIRST 1 ROW ONLY) T1
WITH UR


Please check this bold condition....how can you compare three values with one =Y.... icon_idea.gif
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
No new posts query to fetch record which has only ... maxsubrat DB2 11 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Having a problem FTP'ng to the MF pahiker All Other Mainframe Topics 2 Fri Nov 10, 2017 8:20 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us