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
 

 

-811 SQL code in DB2

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

New User


Joined: 13 Jul 2006
Posts: 5
Location: chennai

PostPosted: Thu Jan 24, 2008 6:58 pm    Post subject: -811 SQL code in DB2
Reply with quote

which row will be there in DCLGEN if a select statement fetches more than one row and with -811 SQL code?

Is "fetch first row only" fetches the same row, as the row fetched if we handle -811 SQL code?
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Jan 24, 2008 7:13 pm    Post subject:
Reply with quote

Depending the output of an operation that completes with an error code is a very bad practice. It depends on the version of DB2.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jan 24, 2008 7:16 pm    Post subject:
Reply with quote

there are some who will try to tell you that you can anticipate which row of the result table will be used to populate your host variables.

but, since you can not use an order by for a singleton select, you will never beable to say in which order the results table will be populated.

there are several reasons to look for an -811:
1. you want to know if there are 0, 1 or more than one row that satisfies your where conditions. Here you do not care about the populated host variable, you just want to know how many rows there are without using a count(*) - or a count(*) is not feasable because you have a group by.

2. you want to verify that a certain column(s) value exists. Here you have probably loaded the host variable with the same value as the where clause. again, you do not care which row has populated your host variables.

If your selected/populated host variable can have more than one value, you should tighten up your where clause or use a cursor.

If you plan on betting which result row is used to populate your host variables, you should start another career.
Back to top
View user's profile Send private message
sasidarauthur

New User


Joined: 13 Jul 2006
Posts: 5
Location: chennai

PostPosted: Thu Jan 24, 2008 7:32 pm    Post subject: Reply to: -811 SQL code in DB2
Reply with quote

thanks for the staements,
now the problem is migrating version 7 to V8, so i need to 'Fetch first row only' for handling -811 condition, previiously -811 was handled and working fine now i need to change that -811 handling, output for these are different, how to handle?

thanks
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 25, 2008 12:09 am    Post subject:
Reply with quote

Hello sasidarauthur and welcome to the forums,

You could declare a cursor. . . .
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 How to Capture the Max return code of... anilkumar922 All Other Mainframe Topics 0 Thu Jan 12, 2017 12:02 pm
This topic is locked: you cannot edit posts or make replies. Calling REXX code from ISPF panel sanchita dey TSO/ISPF 4 Mon Jan 02, 2017 5:13 pm
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts IDEAL - Code Retrieval yugendran CA Products 0 Fri Nov 25, 2016 3:27 pm


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