Order BY with FETCH 1 Rows Only DB2 v7.1 not allowed

becoolmun

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!

Nov 12 '05 #1

Tonkuma

How about this?
(I don't know about performance.)
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
AND C.PROD_NUM = :PROD_NUM
WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */
AND D.SEQNO
= (SELECT MIN(Dm.SEQNO)
FROM DS.Supplier Dm
INNER JOIN
DS.Supplier_CPCTY Cm
ON Dm.Supplier = Cm.Supplier
AND Cm.PROD_NUM = :PROD_NUM
WHERE Dm.SEQNO > :SEQNO
)
;
If only one matching DS.Supplier_CPCTY for each DS.Supplier is always
exists,
The query could be simplified a little.

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
AND C.PROD_NUM = :PROD_NUM
WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */
AND D.SEQNO
= (SELECT MIN(Dm.SEQNO)
FROM DS.Supplier Dm
WHERE Dm.SEQNO > :SEQNO
)
;

Interestingly enough, I can execute the Select with Order By and Fetch First 1 Rows Only in SPUFI, with no problem. <<

In DB2 UDB for OS/390 and z/OS SQL Reference Version 7
Chapter 4. Queries -> select-statement
Both order-by-clause and fetch-first-clause can be specified.
Chapter 5. Statements -> SELECT INTO
Only FETCH FIRST [1] ROW ONLY can be specified.

Nov 12 '05 #2

becoolmun

Thanks, Tonkuma!
I wasn't able to use your suggestion quite as provided, I was able to
break it up into two queries. the first one just gets the next
sequence number and then I just plug it in the second query as an "="
predicate.

I read the DB2 manual chapters you refer to, but the Select with "INTO"
does not allow "ORDER BY" period. And I can't think of anyway to
receive the table values without an "INTO", so I can't use "ORDER BY"
or "FIRST ROW ONLY".

I believe I read that V8 may support this, which doesn't help me at the
moment.
Thanks again!

Tonkuma wrote:

How about this?
(I don't know about performance.)
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
AND C.PROD_NUM = :PROD_NUM
WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */
AND D.SEQNO
= (SELECT MIN(Dm.SEQNO)
FROM DS.Supplier Dm
INNER JOIN
DS.Supplier_CPCTY Cm
ON Dm.Supplier = Cm.Supplier
AND Cm.PROD_NUM = :PROD_NUM
WHERE Dm.SEQNO > :SEQNO
)
;
If only one matching DS.Supplier_CPCTY for each DS.Supplier is always
exists,
The query could be simplified a little.

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
AND C.PROD_NUM = :PROD_NUM
WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */
AND D.SEQNO
= (SELECT MIN(Dm.SEQNO)
FROM DS.Supplier Dm
WHERE Dm.SEQNO > :SEQNO
)
;

Interestingly enough, I can execute the Select with Order By and Fetch First 1 Rows Only in SPUFI, with no problem. <<

In DB2 UDB for OS/390 and z/OS SQL Reference Version 7
Chapter 4. Queries -> select-statement
Both order-by-clause and fetch-first-clause can be specified.
Chapter 5. Statements -> SELECT INTO
Only FETCH FIRST [1] ROW ONLY can be specified.

Nov 12 '05 #3

Robert

be*******@yahoo.com wrote:

Thanks, Tonkuma!
I wasn't able to use your suggestion quite as provided, I was able to
break it up into two queries. the first one just gets the next
sequence number and then I just plug it in the second query as an "="
predicate.

I read the DB2 manual chapters you refer to, but the Select with "INTO"
does not allow "ORDER BY" period. And I can't think of anyway to
receive the table values without an "INTO", so I can't use "ORDER BY"
or "FIRST ROW ONLY".

I believe I read that V8 may support this, which doesn't help me at the
moment.
Thanks again!

Tonkuma wrote:

How about this?
(I don't know about performance.)
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
AND C.PROD_NUM = :PROD_NUM
WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */
AND D.SEQNO
= (SELECT MIN(Dm.SEQNO)
FROM DS.Supplier Dm
INNER JOIN
DS.Supplier_CPCTY Cm
ON Dm.Supplier = Cm.Supplier
AND Cm.PROD_NUM = :PROD_NUM
WHERE Dm.SEQNO > :SEQNO
)
;
If only one matching DS.Supplier_CPCTY for each DS.Supplier is always
exists,
The query could be simplified a little.

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
AND C.PROD_NUM = :PROD_NUM
WHERE D.SEQNO > :SEQNO /* ie. GT the previous Seqno */
AND D.SEQNO
= (SELECT MIN(Dm.SEQNO)
FROM DS.Supplier Dm
WHERE Dm.SEQNO > :SEQNO
)
;

> Interestingly enough, I can execute the Select with Order By and Fetch First 1 Rows Only in SPUFI, with no problem. <<

In DB2 UDB for OS/390 and z/OS SQL Reference Version 7
Chapter 4. Queries -> select-statement
Both order-by-clause and fetch-first-clause can be specified.
Chapter 5. Statements -> SELECT INTO
Only FETCH FIRST [1] ROW ONLY can be specified.

I haven't used DB2 for quite a while now, but seem to remember using
GROUP BY and HAVING to fiddle sequencing when ORDER BY was not allowed.
Might be worth a try.

Robert

Nov 12 '05 #4