-811 when there is only 1 row



Get solution for your ABEND Codes & System Error Messages, SQL Codes, File Status etc...

-811 when there is only 1 row

Postby jyothp12 » Mon Oct 17, 2016 3:26 pm

Hi,

My program has a select statement and it returns -811 as sql code, but when I check the table with same key, the table has only 1 row.

Selecr Query in pgm
-------------------------
SELECT PROFNUM, CUSTNUM, COUNTRY, OPRTIME, OPRUSER, STATUS, INAKTIME, INAKUSER                                
INTO    : DCLVFUPPP.PROFNUM,  
    :DCLVFUPPP.CUSTNUM,  
    :DCLVFUPPP.COUNTRY,  
    :DCLVFUPPP.OPRTIME,  
    :DCLVFUPPP.OPRUSER,  
    :DCLVFUPPP.STATUS,  
    :DCLVFUPPP.INAKTIME :Ws-INAKTIME-INDI,  
    :DCLVFUPPP.INAKUSER :Ws-INAKUSER-INDI                              
FROM    VFUPPP                                    
WHERE   CUSTNUM  = : DCLVFUPPP.CUSTNUM              
  AND   AKTIV = 'A'                                
 

I checked table for same customer num and got only 1 row where as above query in program is giving -811.

Please help.

Thanks in Advance,
Jo
jyothp12
 
Posts: 14
Joined: Thu Dec 22, 2011 12:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: -811 when there is only 1 row

Postby enrico-sorichetti » Mon Oct 17, 2016 3:38 pm

usually proper written software does not lie ;)
are You really sure of what happens in Your program ?

and why <reread> the key when You already have it

SELECT ... , CUSTNUM, ...                          
INTO  ...
    :DCLVFUPPP.CUSTNUM,  
..                      
WHERE   CUSTNUM  = : DCLVFUPPP.CUSTNUM              
 ...                            
 
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: -811 when there is only 1 row

Postby jyothp12 » Mon Oct 17, 2016 3:45 pm

Hi Enrico,
Yes im sure wahts happening in my program, because I did a debug using RDZ to find out where it is getting the sql code.

About the reread, actually this is not my program and i got an email from user telling they are getting this error, so I have to fix it. And I think reread is not related to this -811.

Thanks
Jo
jyothp12
 
Posts: 14
Joined: Thu Dec 22, 2011 12:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: -811 when there is only 1 row

Postby enrico-sorichetti » Mon Oct 17, 2016 3:50 pm

And I think reread is not related to this -811.

I was just pointing out a questionable/confusing approach
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: -811 when there is only 1 row

Postby enrico-sorichetti » Mon Oct 17, 2016 5:18 pm

Yes im sure wahts happening in my program, because I did a debug using RDZ to find out where it is getting the sql code.

I wouldn' t be too much sure, for what reason should DB2 lie to You ???

the manual is pretty clear about the meaning of a -811
http://www.ibm.com/support/knowledgecen ... /n811.html

-811
THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE
Explanation

Execution of an embedded SELECT statement or a subselect in the SET clause of an UPDATE statement has resulted in a result table that contains more than one row. Alternatively, a subquery contained in a basic predicate has produced more than one value.

System action

The statement cannot be processed.

Programmer response

Examine the syntax of the statement to ensure that it contains the proper condition specifications. If the statement syntax is correct, there might be a problem with the data that is causing more than one row or value to be returned when you do not expect it.

SQLSTATE

21000
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: -811 when there is only 1 row

Postby Robert Sample » Mon Oct 17, 2016 7:54 pm

One of the real problems on this forum is people posting because they don't believe the computer:
My program has a select statement and it returns -811 as sql code, but when I check the table with same key, the table has only 1 row.
As long as you persist in thinking the computer is wrong, you will not be able to resolve your problem. The manual quote indicates there are multiple possible reasons for the -811 SQL code, yet you are focusing on only one of them and saying that the message is wrong. It may be wrong for what you are looking at, but the other possible source could be right and hence the computer would not be lying to you. In my experience, computers rarely lie in their output. You need to change your attitude from "I'm right and the computer is wrong" to "I'm getting an error message from the computer, what could be causing it?"
Robert Sample
Global moderator
 
Posts: 3719
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times

Re: -811 when there is only 1 row

Postby jyothp12 » Mon Oct 17, 2016 8:21 pm

@ Robert: I never said that "computer is wrong and I am right". All I asked is for help when I got -811 where there is only one row in the table. By asking for help I meant the same question "what could be causing it". And I think attitude of a person cannot be judged just by looking at a sentence. Could you please tell me what all are the other possible reasons for -811 sql code? Thanks, Jo
jyothp12
 
Posts: 14
Joined: Thu Dec 22, 2011 12:37 pm
Has thanked: 0 time
Been thanked: 0 time

Re: -811 when there is only 1 row

Postby enrico-sorichetti » Mon Oct 17, 2016 8:31 pm

Yes You did ...
Yes im sure wahts happening in my program, because I did a debug using RDZ to find out where it is getting the sql code.


Could you please tell me what all are the other possible reasons for -811 sql code?

why not research the manual yourself ?

And I think attitude of a person cannot be judged just by looking at a sentence.

we are judging based on the whole discussion

the topic is going to be locked shortly ( it is leading nowhere )
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: -811 when there is only 1 row

Postby Robert Sample » Mon Oct 17, 2016 8:49 pm

Could you please tell me what all are the other possible reasons for -811 sql code?
Why should I do this when you have ALREADY been provided the manual explanation for -811, which tells you the other possible reason(s)?

Please note that multiple moderators are telling you that you believe the computer to be wrong, not just me, so you need to take a really close look at yourself and how you post things.

All I asked is for help when I got -811 where there is only one row in the table
There is an assumption here that you know there is only one row in the table returned for your query. Possible refutations for this assumption:
1. You are looking at one system while the job is running on another system.
2. You are using a different customer number than the computer is.
I'm sure there are other possible reasons for the difference between what you think and what the computer is telling you. But the bottom line is, you think you are right and the computer is wrong -- and as long as you think that, NOBODY can help you.
Robert Sample
Global moderator
 
Posts: 3719
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times

Re: -811 when there is only 1 row

Postby jyothp12 » Mon Oct 17, 2016 9:08 pm

Sorry to all if you felt that my attitude was not correct. I will try to find out why the issue is happening. Sorry for wasting your time and thanks for all your help.
jyothp12
 
Posts: 14
Joined: Thu Dec 22, 2011 12:37 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to ABENDS & SQL Codes