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? 26 17145
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.
What DB2 version and platform are you using?
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
"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.
"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.
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.
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?
--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.
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?
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
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
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
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?
--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
--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
>> 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.
--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.
--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
--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
.-,-------------.
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
>> 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.
>> 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 >>
--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
--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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Guy Hocking |
last post by:
Hi there,
I have a problem in my ASP/SQL Server application i am developing, i hope
you guys can help.
I have a ASP form with list boxes populated by SQL tables. When a user
selects a value...
|
by: mlke |
last post by:
I would like to select the top 10 record from a table? How can I do it?
In MS SQL, it's easy using select top 10 from table1. But in oracle database,
I can't use top, anyone have any suggestions?...
|
by: Jay K |
last post by:
Hi,
I have multiple queries like this:
SELECT col1, col2, col3, col4 FROM table1, table2
where table1.col1 = table2.col1 and table1.col2 = 1
ORDER BY col3 desc LIMIT 5
and
|
by: malcolm |
last post by:
Example, suppose you have these 2 tables
(NOTE: My example is totally different, but I'm simply trying to setup
the a simpler version, so excuse the bad design; not the point here)
CarsSold {...
|
by: war_wheelan |
last post by:
I am very new to Transact-SQL programming and don't have a programming
background and was hoping that someone could point me in the right
direction. I have a SELECT statement SELECT FIXID,...
|
by: TP |
last post by:
Here is my problem.
I need to display a table about which I have no information except the
table name. Using metadata I can somehow show the column names and
record values.
But my table has 1...
|
by: Jimmy |
last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:
<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) *...
|
by: rcamarda |
last post by:
I was looking through our vendors views, searching for something I
needed for our Datawarehouse and I came across something I do not
understand: I found a view that lists data when I use it in...
|
by: FishVal |
last post by:
Hereby I'm proposing a way of convinient work with properties containing SQL Select statements, particulary RowSource property of ComboBox and ListBox.
The usual way is the following.
Private...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |