Page 1 of 2

-811 when there is only 1 row

PostPosted: Mon Oct 17, 2016 3:26 pm
by jyothp12
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

Re: -811 when there is only 1 row

PostPosted: Mon Oct 17, 2016 3:38 pm
by enrico-sorichetti
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              
 ...                            
 

Re: -811 when there is only 1 row

PostPosted: Mon Oct 17, 2016 3:45 pm
by jyothp12
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

Re: -811 when there is only 1 row

PostPosted: Mon Oct 17, 2016 3:50 pm
by enrico-sorichetti
And I think reread is not related to this -811.

I was just pointing out a questionable/confusing approach

Re: -811 when there is only 1 row

PostPosted: Mon Oct 17, 2016 5:18 pm
by enrico-sorichetti
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

Re: -811 when there is only 1 row

PostPosted: Mon Oct 17, 2016 7:54 pm
by Robert Sample
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?"

Re: -811 when there is only 1 row

PostPosted: Mon Oct 17, 2016 8:21 pm
by jyothp12
@ 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

Re: -811 when there is only 1 row

PostPosted: Mon Oct 17, 2016 8:31 pm
by enrico-sorichetti
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 )

Re: -811 when there is only 1 row

PostPosted: Mon Oct 17, 2016 8:49 pm
by Robert Sample
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.

Re: -811 when there is only 1 row

PostPosted: Mon Oct 17, 2016 9:08 pm
by jyothp12
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.