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

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?

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

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.

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

Even this does not work.

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

What DB2 version and platform are you using?

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

GreatAlterEgo wrote:
[color=blue]
> 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.[/color]

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

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

"GreatAlterEgo" <greatalterego@gmail.com> wrote in message
news:1137756081.537084.265200@z14g2000cwz.googlegr oups.com...[color=blue]
> Even this does not work.
>[/color]

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.

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

"Knut Stolze" <stolze@de.ibm.com> wrote in message
news:dqql8q$29a$1@lc03.rz.uni-jena.de...[color=blue]
> GreatAlterEgo wrote:
>[color=green]
>> 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.[/color]
>
> 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[/color]

If it is COBOL, then it may be DB2 for z/OS.

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

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.

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

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?

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

--CELKO-- wrote:[color=blue]
> 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?[/color]

Please stop lecturing. Start reading instead.

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

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" <greatalterego@gmail.com> wrote in message
news:1137752867.488355.146290@g49g2000cwa.googlegr oups.com...[color=blue]
> 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?
>[/color]

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

Mark Yudkin wrote:[color=blue]
> 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" <greatalterego@gmail.com> wrote in message
> news:1137752867.488355.146290@g49g2000cwa.googlegr oups.com...
>[color=green]
>>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?
>>[/color][/color]
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

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

Serge Rielau wrote:
[color=blue]
> My guess is that the OP simple has an issue with the ordering of the
> clauses as was suggested by others.[/color]

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

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

Bernd Hohmann wrote:[color=blue]
> Serge Rielau wrote:
>[color=green]
>> My guess is that the OP simple has an issue with the ordering of the
>> clauses as was suggested by others.[/color]
>
>
> 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.[/color]
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

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

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?

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

--CELKO-- wrote:[color=blue]
> 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?[/color]

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

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

--CELKO-- wrote:
[color=blue]
> 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?[/color]

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

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

>> 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.

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

--CELKO-- wrote:[color=blue][color=green][color=darkred]
>>> The OP selects the first row from the table after[/color][/color]
> a certain order was implied. <<
>
> What the hell is this" implied order"? [/color]

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.

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

--CELKO-- wrote:[color=blue][color=green][color=darkred]
>>>The OP selects the first row from the table after[/color][/color]
>
> 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.
>[/color]
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

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

--CELKO-- wrote:
[color=blue][color=green][color=darkred]
>>> The OP selects the first row from the table after[/color][/color]
> a certain order was implied. <<
>
> What the hell is this" implied order"?[/color]

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.
[color=blue]
> 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?[/color]

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

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

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

.-,-------------.
V |[color=blue][color=green]
>>- select-clause --INTO---- host-variable -+-- from-clause ---------->[/color][/color]
[color=blue]
>--+--------------+--+-----------------+--+---------------+----->[/color]
'- where-clause -' '- group-by-clause -' '- having-clause -'
[color=blue]
>--+-----------------+--+--------------------+------------------>[/color]
'- order-by-clause -' '- fetch-first-clause -'
[color=blue]
>--+------------------+----------------------------------------><[/color]
'- 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:[color=blue][color=green][color=darkred]
>>> FOR READ ONLY
>>> OPTIMIZE FOR 1 ROW[/color][/color][/color]

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" <srielau@ca.ibm.com> wrote in message
news:43gvh3F1lrdq1U1@individual.net...[color=blue]
> Mark Yudkin wrote:[color=green]
>> 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" <greatalterego@gmail.com> wrote in message
>> news:1137752867.488355.146290@g49g2000cwa.googlegr oups.com...
>>[color=darkred]
>>>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?
>>>[/color][/color]
> 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[/color]

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

>> 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.

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

>> DATATRIEVE ..<,

You remember DATATRIEVE? How about EasyTrieve on mainframes?
[color=blue][color=green]
>> So I had the idea that modern software would do more. <<[/color][/color]

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 >>

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

--CELKO-- wrote:[color=blue][color=green][color=darkred]
>>>Granted, it is not exactly SQL as it was invented 20 years ago or so. <<[/color][/color]
>
>
> 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.
>[/color]
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

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

--CELKO-- wrote:
[color=blue][color=green][color=darkred]
>>> DATATRIEVE ..<,[/color][/color]
>
> You remember DATATRIEVE? How about EasyTrieve on mainframes?[/color]

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.

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

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