Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Use of FETCH in a query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 216
Location: India

PostPosted: Tue Sep 23, 2008 10:08 am    Post subject: Use of FETCH in a query
Reply with quote

Hi,

I have a Query.It is retrieving many records.But i am taking the first record only using FETCH FIRST 1 ROW ONLY.Will this cause -811 abend if i do not use Cursor.
Back to top
View user's profile Send private message

ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Tue Sep 23, 2008 10:27 am    Post subject:
Reply with quote

If you provide FETCH FIRST 1 ROW ONLY, you won't get -811 abend even w/o using cursor. Refer the following information.

Quote:

Prior to V7, when you coded a SELECT INTO in your application, you had to insure that only one row was return or you would end up with a -811 instead of an answer. Then in V7, a new clause was introduced for the SELECT statement. You could now code a FETCH FIRST n ROWS ONLY clause on the SELECT and control the number of rows returned. In the case of out -811, FETCH FIRST 1 ROW ONLY would satisfy the requirement of a SELECT INTO only allowing one row returned. But new feature did come with some restrictions.

Well, in Version 8, one of those restrictions was removed. In V8, you can code an ORDER BY and FETCH FIRST on the SELECT INTO statement. When combined on the same statement, an answer set is created, sorted, and then the first n rows are retuned to the application. If this is a SELECT INTO, the n would be set to 1.

SELECT FROM INTO :host_variable WHERE ORDER BY FETCH FIRST 1 ROW ONLY
So now if you are simply looking for the highest, lowest, first, or last row in an answer set and you only what to return that one row from the answer set to your application without having to code a cursor, it can be easily accomplished.
Back to top
View user's profile Send private message
birdy K

New User


Joined: 05 Mar 2008
Posts: 72
Location: chennai

PostPosted: Tue Sep 23, 2008 10:48 am    Post subject:
Reply with quote

Hi KSK,

I know about Fetch first 1 row. U have given as we can use order by clause in V8. I am using V8. I tried with order by clause which is giving error. Please confirm whether we can use order by clause.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Sep 23, 2008 10:50 am    Post subject:
Reply with quote

KSK,
what is the source of your quote?
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Tue Sep 23, 2008 11:03 am    Post subject:
Reply with quote

Dick,

I have provided the above information from the below link.

http://it.toolbox.com/blogs/db2zos/interesting-v8-sql-change-13978
Back to top
View user's profile Send private message
sri_mf

Active User


Joined: 31 Aug 2006
Posts: 216
Location: India

PostPosted: Tue Sep 23, 2008 2:11 pm    Post subject: Reply to: Use of FETCH in a query
Reply with quote

Thanks to every one for all the replies.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us