View previous topic :: View next topic
|
Author |
Message |
Rindia
New User
Joined: 17 Jun 2009 Posts: 6 Location: Hyderabad
|
|
|
|
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 |
|
|
Rindia
New User
Joined: 17 Jun 2009 Posts: 6 Location: Hyderabad
|
|
|
|
In other conditions ORDER BY clause exits. |
|
Back to top |
|
|
bhairon singh rathore
New User
Joined: 19 Jun 2008 Posts: 91 Location: banglore
|
|
|
|
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.... |
|
Back to top |
|
|
|