IBM Mainframe Forum

by **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 all

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

by **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 all

SELECT ... , CUSTNUM, ...                           
INTO  ... 
    :DCLVFUPPP.CUSTNUM,  
..                      
WHERE   CUSTNUM  = : DCLVFUPPP.CUSTNUM              
 ...                             
 

by **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

by **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

by **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

by **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?"

by **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

by **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 )

by **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.

by **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.

IBM Mainframe Forum

-811 when there is only 1 row

-811 when there is only 1 row

Re: -811 when there is only 1 row

Re: -811 when there is only 1 row

Re: -811 when there is only 1 row

Re: -811 when there is only 1 row

Re: -811 when there is only 1 row

Re: -811 when there is only 1 row

Re: -811 when there is only 1 row

Re: -811 when there is only 1 row

Re: -811 when there is only 1 row