View previous topic :: View next topic |
Author |
Message |
vini Intermediate
Joined: 12 Jan 2004 Posts: 240 Topics: 48 Location: Maryland
|
Posted: Wed Feb 11, 2004 11:59 am Post subject: Conditional Processing thru Singleton SELECT |
|
|
Hi ,
Does anyone know of a way to do the following in the Where Clause of a Singleton SELECT ...instead of programatically (thru cursor i.e) ?!!
What I intend to do is ...have the Query below modified to qualify One Row based on following ...
Fact: Potentially there can be an Active UI as well as an Active DUA row for a Claim (Clm) or either of the one.
Logic : If there are Rows found for Both the Program Codes then UI Row should qualify instead of DUA Row (UI has higher precedence or priority) .
SELECT MON_PROG_CD
FROM MON
Where FK_CLM_ID = :CLM-ID
And MON_PROG_CD IN ('UI' , 'DUA')
And MON_ACTIVE_IND = 'Y'
Vini
Note: Cannot use ORDER BY in Singleton Select in DB2.. |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
|
Posted: Wed Feb 11, 2004 2:25 pm Post subject: |
|
|
Vini,
If you DB2 version 7 then you can use FETCH FIRST 'N' rows TO get only UI row when both DUA and UI exist.
Code: |
SELECT MON_PROG_CD
FROM MON
WHERE FK_CLM_ID = :CLM_ID
AND MON_PROG_CD IN('UI','DUA')
ORDER BY MON_PROG_CD DESC
FETCH FIRST 1 ROW ONLY
;
|
Try this sql if you don't have db2 version 7
Code: |
SELECT A.MON_PROG_CD
FROM MON A
WHERE A.FK_CLM_ID = :CLM_ID
AND A.MON_PROG_CD = 'UI'
OR (A.MON_PROG_CD = 'DUA' AND NOT EXISTS (SELECT B.MON_PROG_CD
FROM MON B
WHERE B.FK_CLM_ID = :CLM_ID
AND B.MON_PROG_CD = 'DUA'))
;
|
Hope this helps...
Cheers
Kolusu _________________ Kolusu
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
vini Intermediate
Joined: 12 Jan 2004 Posts: 240 Topics: 48 Location: Maryland
|
Posted: Wed Feb 11, 2004 2:57 pm Post subject: |
|
|
Kolusu,
I got following error despite using DB2 SQL PRECOMPILER VERSION 7 REL. 1.0 in the 'singleton' Select when I used the first solution.
DB2 SQL PRECOMPILER MESSAGES
DSNH109I E DSNHAPLY LINE 3355 COL 15 "ORDER BY" CLAUSE NOT PERMITTED
I will test run the second soln.
Thnks
Vini |
|
Back to top |
|
|
vini Intermediate
Joined: 12 Jan 2004 Posts: 240 Topics: 48 Location: Maryland
|
Posted: Wed Feb 11, 2004 7:31 pm Post subject: |
|
|
Kolusu ,
I test ran the Query on MON which had variety of data as follows :
1) Both UI & DUA Rows for a claim
2) Only UI Row for a claim
3) Only DUA Row for a claim
It worked well for Claims of type 1 and 2 !
However it did not work for type 3 ..in which case it returned nothing ...when it should return DUA if thats the only row. Maybe I was not clear with the requirement in the first place .
If anyone can think of some way ,it would be great.
Thnks
Vini |
|
Back to top |
|
|
|
|