MVSFORUMS.com

vini

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..

kolusu · 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.

vini · 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

vini · 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