Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 215
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: 6968
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: 215
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us