It seems with V7 of DB2 I can now use Fetch First 1 Rows Only in a
Select stmt, whic is great, because I don't have to use a cursor.
Unfortunately, it doesn't allow ORDER BY in the same Select and the
order is important because I need to pick the next supplier in a
sequence that can be changed by the user. Interestingly enough, I can
execute the Select with Order By and Fetch First 1 Rows Only in SPUFI,
with no problem.
Is there some way of handling the sort on Seqno without using Order By.
Here's the Query:
SELECT D.Supplier, C.Gadget_QTY, D.SEQNO
INTO :Supplier
,:Gadget_QTY
,:SEQNO
FROM DS.Supplier D
INNER JOIN DS.Supplier_CPCTY C
ON D.Supplier = C.Supplier
WHERE C.PROD_NUM = :PROD_NUM
AND D.SEQNO > :SEQNO /* ie. GT the previous Seqno */
FETCH FIRST 1 ROWS ONLY;
As you can see, I keep going to the next supplier in sequence until
there are non left - this Select is in a loop.
I'm using PL/I 1.1.1 on Z/OS 1.4
Thanks!