 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / January 2006does 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? 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. Even this does not work. What DB2 version and platform are you using? > 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. > Hi, > This is my query which is embedded in a COBOL program. [quoted text clipped - 16 lines] > 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.  SignatureKnut Stolze DB2 Information Integration Development IBM Germany >> Hi, >> This is my query which is embedded in a COBOL program. [quoted text clipped - 20 lines] > of > "db2level" command) This should be working since level 031219 or later. 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? > 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. > 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. SignatureKnut 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. >>> 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? SignatureIn 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. >>>The OP selects the first row from the table after > [quoted text clipped - 8 lines] > 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 SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab >>> 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. SignatureKnut Stolze DB2 Information Integration Development IBM Germany >> 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. >>>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 SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab 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 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. > Hi, > This is my query which is embedded in a COBOL program. [quoted text clipped - 19 lines] > I now doubt whether DB2 supports SELECT-INTO with ORDER BY. > Does it or does it not? > 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 [quoted text clipped - 26 lines] >>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 SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab > 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 >> 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 SignatureSerge 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? > 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 >> 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 >> >>> 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 SignatureIn 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. .-,-------------. 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". >> SELECT INTO is for singleton selects without cursor positioning. ORDER BY >> is for sorting, which is inapplicable for a select that by definition may [quoted text clipped - 39 lines] > Cheers > Serge |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.