-811 SQL code in DB2

sasidarauthur · New User Joined: 13 Jul 2006 Posts: 5 Location: chennai

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?

Craq Giegerich · Posted: Thu Jan 24, 2008 7:13 pm

Depending the output of an operation that completes with an error code is a very bad practice. It depends on the version of DB2.

dbzTHEdinosauer · Posted: Thu Jan 24, 2008 7:16 pm

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.

sasidarauthur · New User Joined: 13 Jul 2006 Posts: 5 Location: chennai

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

dick scherrer · Posted: Fri Jan 25, 2008 12:09 am

Hello sasidarauthur and welcome to the forums,

You could declare a cursor. . . .