Page 2 of 2
Re: Order By Clause in Singleton Select
Posted:
Fri Jul 24, 2009 5:29 pmby swd
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.
Re: Order By Clause in Singleton Select
Posted:
Fri Jul 24, 2009 10:28 pmby thamodharan
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...
Re: Order By Clause in Singleton Select
Posted:
Tue Aug 11, 2009 4:46 pmby GuyC
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.
Re: Order By Clause in Singleton Select
Posted:
Wed Aug 12, 2009 8:54 pmby thamodharan
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!
Re: Order By Clause in Singleton Select
Posted:
Wed Aug 12, 2009 9:37 pmby GuyC
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.