By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,222 Members | 1,730 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,222 IT Pros & Developers. It's quick & easy.

does DB2 support SELECT - INTO statement with ORDER BY clause?

P: n/a
Hi,
This is my query which is embedded in a COBOL program.

EXEC SQL
SELECT DATE, AGE, DURATION, AMT
INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT
FROM TAB1
WHERE CODE = :KEY.CODE
AND SET = :KEY.SET
AND DATE <= :KEY.DATE
AND AGE >= :KEY.AGE
AND DURATION >= :KEY.DURATION
ORDER BY DATE DESC, AGE ASC, DURATION ASC
FOR READ ONLY
FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW
END-EXEC

When I compile it, the module errors out, giving the reason as :FOR
FETCH ONLY not supported. This, inspite of me not using FOR FETCH ONLY.

I now doubt whether DB2 supports SELECT-INTO with ORDER BY.
Does it or does it not?

Jan 20 '06 #1
Share this Question
Share on Google+
26 Replies


P: n/a
Put "FETCH FIRST 1 ROW ONLY" before "FOR READ ONLY" and "OPTIMIZE FOR 1
ROW".

"FETCH FIRST 1 ROW ONLY" is part of Subselect.
"FOR READ ONLY" and "OPTIMIZE FOR 1 ROW" are part of Select-sttement.

Jan 20 '06 #2

P: n/a
Even this does not work.

Jan 20 '06 #3

P: n/a
What DB2 version and platform are you using?

Jan 20 '06 #4

P: n/a
GreatAlterEgo wrote:
Hi,
This is my query which is embedded in a COBOL program.

EXEC SQL
SELECT DATE, AGE, DURATION, AMT
INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT
FROM TAB1
WHERE CODE = :KEY.CODE
AND SET = :KEY.SET
AND DATE <= :KEY.DATE
AND AGE >= :KEY.AGE
AND DURATION >= :KEY.DURATION
ORDER BY DATE DESC, AGE ASC, DURATION ASC
FOR READ ONLY
FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW
END-EXEC

When I compile it, the module errors out, giving the reason as :FOR
FETCH ONLY not supported. This, inspite of me not using FOR FETCH ONLY.


Do you get a SQL0811? If yes, which level of DB2 are you using? (output of
"db2level" command) This should be working since level 031219 or later.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jan 20 '06 #5

P: n/a
"GreatAlterEgo" <gr***********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Even this does not work.


I don't know for sure, but a fetch into assumes there is only one row, and
order by implies multiple rows. If you open a cursor and then do only one
fetch into from the cursor, the cursor definition can have an order by.
Jan 20 '06 #6

P: n/a

"Knut Stolze" <st****@de.ibm.com> wrote in message
news:dq**********@lc03.rz.uni-jena.de...
GreatAlterEgo wrote:
Hi,
This is my query which is embedded in a COBOL program.

EXEC SQL
SELECT DATE, AGE, DURATION, AMT
INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT
FROM TAB1
WHERE CODE = :KEY.CODE
AND SET = :KEY.SET
AND DATE <= :KEY.DATE
AND AGE >= :KEY.AGE
AND DURATION >= :KEY.DURATION
ORDER BY DATE DESC, AGE ASC, DURATION ASC
FOR READ ONLY
FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW
END-EXEC

When I compile it, the module errors out, giving the reason as :FOR
FETCH ONLY not supported. This, inspite of me not using FOR FETCH ONLY.


Do you get a SQL0811? If yes, which level of DB2 are you using? (output
of
"db2level" command) This should be working since level 031219 or later.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany


If it is COBOL, then it may be DB2 for z/OS.
Jan 20 '06 #7

P: n/a
I think so.
DB2 for OS/390 and z/OS supports "FETCH FIRST n ROW ONLY" from Version
7.

Anyway, if DB2 version and platform are unknown, it would be difficult
to know the reason of error.

Jan 21 '06 #8

P: n/a
Since tables have no order by definition, what are you tryng to do?
Apparently, you want to grab a random row for display. Is that right?

Jan 21 '06 #9

P: n/a
--CELKO-- wrote:
Since tables have no order by definition, what are you tryng to do?
Apparently, you want to grab a random row for display. Is that right?


Please stop lecturing. Start reading instead.
Jan 21 '06 #10

P: n/a
SELECT INTO is for singleton selects without cursor positioning. ORDER BY is
for sorting, which is inapplicable for a select that by definition may only
return one row. Similarly FOR READ ONLY is inappliable for a select that by
definition has no cursor and therefore cannot permit anything but reading.

You need to get your semantics consistent.

"GreatAlterEgo" <gr***********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Hi,
This is my query which is embedded in a COBOL program.

EXEC SQL
SELECT DATE, AGE, DURATION, AMT
INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT
FROM TAB1
WHERE CODE = :KEY.CODE
AND SET = :KEY.SET
AND DATE <= :KEY.DATE
AND AGE >= :KEY.AGE
AND DURATION >= :KEY.DURATION
ORDER BY DATE DESC, AGE ASC, DURATION ASC
FOR READ ONLY
FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW
END-EXEC

When I compile it, the module errors out, giving the reason as :FOR
FETCH ONLY not supported. This, inspite of me not using FOR FETCH ONLY.

I now doubt whether DB2 supports SELECT-INTO with ORDER BY.
Does it or does it not?

Jan 22 '06 #11

P: n/a
Mark Yudkin wrote:
SELECT INTO is for singleton selects without cursor positioning. ORDER BY is
for sorting, which is inapplicable for a select that by definition may only
return one row. Similarly FOR READ ONLY is inappliable for a select that by
definition has no cursor and therefore cannot permit anything but reading.

You need to get your semantics consistent.

"GreatAlterEgo" <gr***********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Hi,
This is my query which is embedded in a COBOL program.

EXEC SQL
SELECT DATE, AGE, DURATION, AMT
INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT
FROM TAB1
WHERE CODE = :KEY.CODE
AND SET = :KEY.SET
AND DATE <= :KEY.DATE
AND AGE >= :KEY.AGE
AND DURATION >= :KEY.DURATION
ORDER BY DATE DESC, AGE ASC, DURATION ASC
FOR READ ONLY
FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW
END-EXEC

When I compile it, the module errors out, giving the reason as :FOR
FETCH ONLY not supported. This, inspite of me not using FOR FETCH ONLY.

I now doubt whether DB2 supports SELECT-INTO with ORDER BY.
Does it or does it not?

The rumours are getting out of hand here.... :-)
In DB2 for LUW SELECT INTO FROM ORDER BY FETCH FIRST ROW ONLY
IS indeed supported. The most coomon usage is
to retrieve the first or last row of a queue table (such as ORDERS).
I know this works in C for sure since it's used in TPC-C.
Don't have COBOL skills to try it out.
My guess is that the OP simple has an issue with the ordering of the
clauses as was suggested by others.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 22 '06 #12

P: n/a
Serge Rielau wrote:
My guess is that the OP simple has an issue with the ordering of the
clauses as was suggested by others.


Wild guess: the Cobol precompiler for SQL statements tries some
optimization by inserting a "for fetch only" clause. So the compiler
manual could help here.

Bernd
Jan 22 '06 #13

P: n/a
Bernd Hohmann wrote:
Serge Rielau wrote:
My guess is that the OP simple has an issue with the ordering of the
clauses as was suggested by others.

Wild guess: the Cobol precompiler for SQL statements tries some
optimization by inserting a "for fetch only" clause. So the compiler
manual could help here.

That would be giving the pre-compiler too much credit... it's a prety
dumb beast

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 22 '06 #14

P: n/a
Instead of making a wild guess, you could look up how the precompiler
works in the manual :) Or ask what they are trying to do. Or would
that be lecturing?

Jan 22 '06 #15

P: n/a
--CELKO-- wrote:
Instead of making a wild guess, you could look up how the precompiler
works in the manual :) Or ask what they are trying to do. Or would
that be lecturing?


Well - as far memory serves the Cobol precompiler for DATATRIEVE was
able to add some elements to the query if isolation level requires
special handling.

So I had the idea that modern software would do more.

My fault :-)

Bernd
Jan 22 '06 #16

P: n/a
--CELKO-- wrote:
Since tables have no order by definition, what are you tryng to do?
Apparently, you want to grab a random row for display. Is that right?


Obviously that's not so. The OP selects the first row from the table after
a certain order was implied. Thus, it is exactly defined which row to get.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jan 23 '06 #17

P: n/a
>> The OP selects the first row from the table after
a certain order was implied. <<

What the hell is this" implied order"? Think about that for a second;
the PHYSICAL ordering depends on the hardware, the data on the
hardware, the indexing, the product release version, etc. And why do
you think that this answer will be the same on another platfrom with
another release of the same product?

SQL Server "hillbillies" who only spoke one dialect instead of real SQL
have been caught on this point more than anyone else.

Jan 23 '06 #18

P: n/a
--CELKO-- wrote:
The OP selects the first row from the table after

a certain order was implied. <<

What the hell is this" implied order"?


The order the original poster had created with his statement. Never read
this posting?

--
In den USA war die letzte Zigarette, die man dem zum Tode Verurteilten
gewährte, eine Tradition, ein angestammtes Recht, bis vor kurzem der
Gouverneur von Alabama diese verbot - und zwar aus gesundheitlichen Gründen.
Jan 23 '06 #19

P: n/a
--CELKO-- wrote:
The OP selects the first row from the table after


a certain order was implied. <<

What the hell is this" implied order"? Think about that for a second;
the PHYSICAL ordering depends on the hardware, the data on the
hardware, the indexing, the product release version, etc. And why do
you think that this answer will be the same on another platfrom with
another release of the same product?

SQL Server "hillbillies" who only spoke one dialect instead of real SQL
have been caught on this point more than anyone else.

Is there anything wrong with this SQL?

CREATE TABLE orders(orderid INT NOT NULL PRIMARY KEY, data INT);

SELECT orderid, data INTO :orderid, :data FROM orders
ORDER BY orderid
FETCH FIRST ROW ONLY

In your SQL is it required to write:

SELECT orderid, data INTO :orderid, :data FROM orders
WHERE orderid = (SELECT MIN(orderid) FROM orders)

If so why is that better, more relations/legal/ethical/....

Just relax... not ALL usages of ORDER BY and FETCH FIRST are evil....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 24 '06 #20

P: n/a
--CELKO-- wrote:
The OP selects the first row from the table after
a certain order was implied. <<

What the hell is this" implied order"?


The order imposed by the ORDER BY. Normally, that ordering is only relevant
if a cursor is used. But I just think about SELECT INTO as some syntactic
sugar to not explicitly declare a cursor, open it, fetch the first row and
then close the cursor again. I think it makes perfect sense there.

Granted, it is not exactly SQL as it was invented 20 years ago or so.
Think about that for a second;
the PHYSICAL ordering depends on the hardware, the data on the
hardware, the indexing, the product release version, etc. And why do
you think that this answer will be the same on another platfrom with
another release of the same product?


What has the original question to do with the physical ordering? Nothing,
nada, zip, zero.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jan 24 '06 #21

P: n/a
.-,-------------.
V |
- select-clause --INTO---- host-variable -+-- from-clause ---------->
--+--------------+--+-----------------+--+---------------+-----> '- where-clause -' '- group-by-clause -' '- having-clause -'
--+-----------------+--+--------------------+------------------> '- order-by-clause -' '- fetch-first-clause -'
--+------------------+---------------------------------------->< '- isolation-clause -'
read-only clause, update-clause optimize-for-clause are not listed.

The original query specifies two clauses that are listed in the
documentation for select into:
FOR READ ONLY
OPTIMIZE FOR 1 ROW


My comment on ORDER BY is wrong of course - I means to say that "OPTIMIZE
FOR 1 ROW is inapplicable for a select that by definition may only return
one row".

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:43*************@individual.net... Mark Yudkin wrote:
SELECT INTO is for singleton selects without cursor positioning. ORDER BY
is for sorting, which is inapplicable for a select that by definition may
only return one row. Similarly FOR READ ONLY is inappliable for a select
that by definition has no cursor and therefore cannot permit anything but
reading.

You need to get your semantics consistent.

"GreatAlterEgo" <gr***********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Hi,
This is my query which is embedded in a COBOL program.

EXEC SQL
SELECT DATE, AGE, DURATION, AMT
INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT
FROM TAB1
WHERE CODE = :KEY.CODE
AND SET = :KEY.SET
AND DATE <= :KEY.DATE
AND AGE >= :KEY.AGE
AND DURATION >= :KEY.DURATION
ORDER BY DATE DESC, AGE ASC, DURATION ASC
FOR READ ONLY
FETCH FIRST 1 ROW ONLY
OPTIMIZE FOR 1 ROW
END-EXEC

When I compile it, the module errors out, giving the reason as :FOR
FETCH ONLY not supported. This, inspite of me not using FOR FETCH ONLY.

I now doubt whether DB2 supports SELECT-INTO with ORDER BY.
Does it or does it not?

The rumours are getting out of hand here.... :-)
In DB2 for LUW SELECT INTO FROM ORDER BY FETCH FIRST ROW ONLY
IS indeed supported. The most coomon usage is
to retrieve the first or last row of a queue table (such as ORDERS).
I know this works in C for sure since it's used in TPC-C.
Don't have COBOL skills to try it out.
My guess is that the OP simple has an issue with the ordering of the
clauses as was suggested by others.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Jan 25 '06 #22

P: n/a
>> Granted, it is not exactly SQL as it was invented 20 years ago or so. <<

Actually, it is a violation of RDBMS as it was defined over 30 years
ago, or math as it was defined from Cantor's papers on sets in the late
1800's. SQL is to RDBMS as FORTRAN is to Algebra.

Jan 31 '06 #23

P: n/a
>> DATATRIEVE ..<,

You remember DATATRIEVE? How about EasyTrieve on mainframes?
So I had the idea that modern software would do more. <<


The kids suck as programmers these days. We were sooo much smarter, we
had cooler clothes, we had fewer bugs, << insert old fart rant here >>

Jan 31 '06 #24

P: n/a
--CELKO-- wrote:
Granted, it is not exactly SQL as it was invented 20 years ago or so. <<

Actually, it is a violation of RDBMS as it was defined over 30 years
ago, or math as it was defined from Cantor's papers on sets in the late
1800's. SQL is to RDBMS as FORTRAN is to Algebra.

Joe,

I've been listening to thsi long enough. Why don't you put money where
your mouth is and explain why ORDER BY and FETCH FIRST is violating
relational algebra. If you wish you can also take this offline.
I can half follow you on the IDENTITY, but here I'm lost...
To me any operator that consumes one or more relational table and spits
out a relation table is just fine.
A SELECT with an ORDER BY and FETCH FIRST property is doing just that.
No harm done.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 31 '06 #25

P: n/a
--CELKO-- wrote:
DATATRIEVE ..<,


You remember DATATRIEVE? How about EasyTrieve on mainframes?


I used DATATRIEVE on a VAX-11 so there was no need for a bulky mainframe
iron :-)

Bernd

--
In den USA war die letzte Zigarette, die man dem zum Tode Verurteilten
gewährte, eine Tradition, ein angestammtes Recht, bis vor kurzem der
Gouverneur von Alabama diese verbot - und zwar aus gesundheitlichen Gründen.
Jan 31 '06 #26

P: n/a
Which FORTRAN? The early version or the latest ANSI standard one? And
FORTRAN from which vendor? Every FORTRAN compiler maker has their own
extensions to the language.

The same thing with SQL. There's been different, although evolving,
verions throughout the years as well as different packages from
different vendors.

The the latest ansi standard version is a great place to start but the
standard was (as I understand it) never meant to be complete or to
cover every possible scenario. It's meant a s a guide and to be used
as often as practical.

And isn't the ORDER BY clause part of the ansi standard?

Patrick

Jan 31 '06 #27

This discussion thread is closed

Replies have been disabled for this discussion.