Order By Clause in Singleton Select



IBM's flagship relational database management system

Re: Order By Clause in Singleton Select

Postby swd » Fri Jul 24, 2009 5:29 pm

I've just realised that there could be different CUST_NMBR values, so the above suggestion won't be any good. Looks like you're going to have to do that cursor.
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

Re: Re: Order By Clause in Singleton Select

 

Re: Order By Clause in Singleton Select

Postby thamodharan » Fri Jul 24, 2009 10:28 pm

Hi,

What version of db2 is used on your system? Which version of COBOL?

I am using DB2 8.1 and VS COBOL II.

In front of this "DSNH109I", is there an IGYxxxx message id?

There was no message id like IGYxxxx in front of 'DSNH1091'.

Now I realized and stopped trying to avoid the cursor.

Thanks for your quick responses...
thamodharan
 
Posts: 6
Joined: Wed Jun 17, 2009 3:09 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Order By Clause in Singleton Select

Postby GuyC » Tue Aug 11, 2009 4:46 pm

SELECT CUST_NMBR, LAST_UPDT_DT
INTO :WS-CUST-NMBR,:WS-LAST-UPDT
FROM CUST A
WHERE END_DT >= :WS-PARM-DATE
AND EFF_DT <= :WS-PARM-DATE
and last_upd_dt = (select max(B.ws-last-updt) from cust B where A.cust_nmbr = B.cust_nmbr and
b.END_DT >= :WS-PARM-DATE
AND b.EFF_DT <= :WS-PARM-DATE
)


without the sort , is another way of doing it.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Order By Clause in Singleton Select

Postby thamodharan » Wed Aug 12, 2009 8:54 pm

Hi,

Already I tried this query. The disadvantage with this query was the time. It was taking more time when compared to the cursor.

Thanks!
thamodharan
 
Posts: 6
Joined: Wed Jun 17, 2009 3:09 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Order By Clause in Singleton Select

Postby GuyC » Wed Aug 12, 2009 9:37 pm

I just noticed that the query I gave was not equivalent to the query in post 1.
Like SWD said there is no "CUST_NMBR = :WS-cust-nmbr " in the where clause,thus the result could be more than one customer.

If cust_nmbr was in the where clause
you could just SELECT MAX(last_updt) ... like SWD suggested.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Previous

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post