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
 

 

How to avoid -811 error In embedded sql

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

New User


Joined: 18 Mar 2005
Posts: 48

PostPosted: Mon May 23, 2005 11:44 am    Post subject: How to avoid -811 error In embedded sql
Reply with quote

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.
Back to top
View user's profile Send private message

amolgijare

New User


Joined: 19 Mar 2005
Posts: 8
Location: Pune, India

PostPosted: Mon May 23, 2005 2:49 pm    Post subject: Re: -811 error
Reply with quote

try using cursors.....
hope this helps
- Amol
Back to top
View user's profile Send private message
MGIndaco

Active User


Joined: 10 Mar 2005
Posts: 432
Location: Milan, Italy

PostPosted: Mon May 23, 2005 5:24 pm    Post subject:
Reply with quote

SQL abend error -811 usually denote a duplicate value as result of a query
This is from the manual:
Quote:
-811 THE RESULT OF AN EMBEDDED SELECT STATEMENT IS A TABLE OF MORE THAN
ONE ROW, OR THE RESULT OF THE SUBQUERY OF A BASIC PREDICATE IS MORE
THAN ONE VALUE

To avoid this error you must to check the definition of the tables that you are joining and assure you that no duplicate value are produced by a subquery.
To assure your result, if it's possible, use in subquery a DISTINCT.
Back to top
View user's profile Send private message
knd_r
Currently Banned

New User


Joined: 18 Mar 2005
Posts: 48

PostPosted: Mon May 23, 2005 7:40 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
chayanath

New User


Joined: 07 Mar 2005
Posts: 6
Location: vijayawda

PostPosted: Mon May 23, 2005 9:30 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
sreehari

New User


Joined: 07 Feb 2007
Posts: 1
Location: Chennai

PostPosted: Sun Mar 11, 2007 1:58 pm    Post subject: Re: -811 error In embedded sql
Reply with quote

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
Back to top
View user's profile Send private message
Santoshdorge

New User


Joined: 27 Jun 2006
Posts: 48
Location: Pune

PostPosted: Mon Mar 12, 2007 10:20 am    Post subject:
Reply with quote

Hi


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



thanks
Santosh
Back to top
View user's profile Send private message
skkp2006

New User


Joined: 14 Jul 2006
Posts: 93
Location: Chennai,India

PostPosted: Mon Mar 12, 2007 11:44 am    Post subject: Re: -811 error In embedded sql
Reply with quote

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
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Mon Mar 12, 2007 12:27 pm    Post subject:
Reply with quote

Quote:
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


If you want to see how many rows are satisfied for that condition, then take COUNT(*). If you want to fetch the rows, then you have to go for CURSOR'S. This is a trade off for your scenario. No other option for you.
Singleton select can give one & only one row.[/b]
Back to top
View user's profile Send private message
kgumraj

Active User


Joined: 01 May 2006
Posts: 151
Location: Hyderabad

PostPosted: Fri Mar 16, 2007 1:45 pm    Post subject:
Reply with quote

Hi,

When you handle Sqlerrors, you by pass -811 also, hope that solves.
Back to top
View user's profile Send private message
priyamnavada

New User


Joined: 24 Dec 2005
Posts: 52
Location: hyderabad

PostPosted: Tue May 15, 2007 3:35 pm    Post subject: Re: How to avoid -811 error In embedded sql
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Dec 06, 2007 6:34 pm    Post subject:
Reply with quote

vini_srcna,
Quote:

Singleton select can give one & only one row.


that is true, but on versions before db2V8, your host variables will still be populated when you receive a -811. V8 supposedly will not populate the host variables. (have not tested it yet).

vsn7 and before:
ibm just says that even though the host variables are populated when receiving a -811, the result row used to populate is not known, nor can it be, since you can't use an order by clause on a singleton select.
Back to top
View user's profile Send private message
mbatta1

New User


Joined: 30 Jan 2008
Posts: 3
Location: chennai

PostPosted: Mon Mar 10, 2008 5:19 pm    Post subject: Reply to: How to avoid -811 error In embedded sql
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Mar 10, 2008 5:30 pm    Post subject:
Reply with quote

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?
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 Need help with below DB2 error ashek15 DB2 3 Sat Mar 18, 2017 5:56 am
No new posts FINDREP Syntax Error seahawk789 DFSORT/ICETOOL 4 Thu Mar 09, 2017 10:11 pm
No new posts INVOKE WEBSERVICE error danik56 CICS 11 Thu Mar 09, 2017 11:52 am
No new posts Row not found Sql error GaganGarg DB2 7 Tue Mar 07, 2017 6:29 am
No new posts DB2 SQL Error: SQLCODE=-440, SQLSTATE... kishpra DB2 1 Sat Feb 18, 2017 2:31 am


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