How to avoid -811 error In embedded sql

knd_r · New User Joined: 18 Mar 2005 Posts: 48

hi,

In embedded sql, I am retrieving more than one row using select statement and i am getting -811 error. How to avoid this error and till I have to use that select with more than one row?

bye,
ks reddy.

amolgijare · Posted: Mon May 23, 2005 2:49 pm

try using cursors.....
hope this helps
- Amol

MGIndaco · Posted: Mon May 23, 2005 5:24 pm

SQL abend error -811 usually denote a duplicate value as result of a query
This is from the manual:

knd_r · New User Joined: 18 Mar 2005 Posts: 48

hi amol,

I know how to use cursors, but here the situation is different. please understand the question.

hi mgindaco,

i am using distinct and i am retrieving more than one row in select statement using embedded sql, probably i will get -811 error. I dont want to face this error and i still want that sql statement retrieving more than one row. How to accomplish this?

for example: in cobol if we are reading out of range in a table we will face soc4 error, if we mention nossrange in compiler options then we wont get soc4 and still we can read out of range!

thanks,
ks reddy.

chayanath · New User Joined: 07 Mar 2005 Posts: 6 Location: vijayawda

hello,

cursors r the best way for one to retrieve more than one row at a time.dO FOLLWOING STEPS:
now declare cursor in WS-Section or procedure division as:
1) EXEC SQL
DECLARE <CURNAME> CURSOR FOR
//** < SQL QUERY ON WHICH U WANT TO GET THE DATA>****/
SELECT EMPNO,EMPNAME FROM EMP WHERE EMPN=='200'
END-EXEC.
2)EXEC SQL
OPEN <CURNAME>
END-EXEC.
3)EXEC SQL
FETCH <CURNAME>
END-EXEC.
4)EXEC SQL
CLOSE <CURNAME>
END-EXEC.
2,3 & 4 SHOULD BE IN PROCEDURE DIVISION.
BYE

sreehari · New User Joined: 07 Feb 2007 Posts: 1 Location: Chennai

Hi,

Try to add one more condition in the Where clause of your SQL query wihle joining the table then it will show Both the rows with out throwing the error.

Sreehari

Santoshdorge · New User Joined: 27 Jun 2006 Posts: 48 Location: Pune

Hi

IF you are interested in one row only then try to use fetch first 1 row only
clause.

thanks
Santosh

skkp2006 · Posted: Mon Mar 12, 2007 11:44 am

Select can fetch only one row at a time....otherwise u have to go for cursors... there is no option as to fetch more than one row using a sql select(As in SSRANGE).
And to generalise include all the primary key variables(single/composite) in the where clause to avoid -811.

Regards,
Syam

vini_srcna · Posted: Mon Mar 12, 2007 12:27 pm

kgumraj · Posted: Fri Mar 16, 2007 1:45 pm

Hi,

When you handle Sqlerrors, you by pass -811 also, hope that solves.

priyamnavada · New User Joined: 24 Dec 2005 Posts: 52 Location: hyderabad

Just handle it seperately using WHEN -811 clause and later retrive all the rows using singleton selelct, moving into table.

later use the table for futher.

Hope i answered your doubt.

dbzTHEdinosauer · Posted: Thu Dec 06, 2007 6:34 pm

vini_srcna,

mbatta1 · New User Joined: 30 Jan 2008 Posts: 3 Location: chennai

if you want only a single variable then you can use distinct so that you will get only one value insted of multiple values

if you want to get multiple results then use cursor

dbzTHEdinosauer · Posted: Mon Mar 10, 2008 5:30 pm

mbatta1,

did you bother to notice that this thread is a little old.

also, since the op never did bother to show his/her sql, you really have no idea what the op was trying to do.

do you plan to try to up your post count by responding to all the old threads?