RE: SQL error on an update

Sounds like you have duplicate CUSNR in ALCUSMST.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jeff Crosby
Sent: Tuesday, November 1, 2022 2:03 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL error on an update

All,

We run an equipment items sales report monthly. The CL driver program creates a file of equipment sales in QTEMP. Then there is this statement in the CL:

RUNSQLSTM SRCFILE(DILGARD/QSQLSRC) +
SRCMBR(SH51) +
COMMIT(*NONE)

The source member has this:

UPDATE EQSLSALL
SET EQSLSALL.SLSNR = (SELECT ALCUSMST.SLSNR
FROM ALCUSMST
WHERE EQSLSALL.CUSNR = ALCUSMST.CUSNR
);

and HAS NOT CHANGED SINCE 2011. What the statement is for, is the case where the sales rep changed during the month. (This seldom happens.) The statement puts the currently assigned sales rep into that QTEMP sales file so all records pertaining to a customer are under the current sales rep.

Today the RUNSQLSTM statement failed with this in the joblog:

CPF5387 Escape 50 11/01/22 13:24:42.029819
QDBIOERRQO QSYS *STMT QSQRUN3 QSYS
From module . . . . . . . . :
QDBIOERRQO
From procedure . . . . . . :
SEND_MESSAGE
Statement . . . . . . . . . : 4319

To module . . . . . . . . . :
QSQUPDAT
To procedure . . . . . . . :
SQL_Update
Statement . . . . . . . . . : 16473

Message . . . . : Number of records
retrieved is not correct.
Cause . . . . . : A subselect of a
basic predicate query could not be
processed because more than one record was returned. Recovery . . . :
Do one of the following: -- If you want only one record, specify a
subselect that returns only one record. -- If you want more than one
record, specify the IN, EXISTS, ANY, or ALL predicate.

SQL0811 Diagnostic 30 11/01/22 13:24:42.031212 QSQRUN3
QSYS *STMT QSQRUN3 Q
From module . . . . . . . . :
QSQUPDAT
From procedure . . . . . . :
CLEANUP
Statement . . . . . . . . . : 32857

To module . . . . . . . . . :
QSQUPDAT
To procedure . . . . . . . :
CLEANUP
Statement . . . . . . . . . : 32857

Message . . . . : Result of SELECT
more than one row.
Cause . . . . . : The result table
of a SELECT INTO statement, a
subquery, or a subselect of a SET statement contains more than one
row. The error type is 2. If the error type is 1 then a SELECT INTO
statement attempted to return more than one row. If the error type is
2 then a subselect of a basic
predicate has produced more than one
row. Only one row is allowed.
Recovery . . . : Change the
selection so that only one result row is
returned and then try the request again. The DECLARE CURSOR, OPEN, and FETCH statements must be used to process more than
one result row. For a subquery the IN, EXISTS, ANY or ALL predicates can be used to process more than one result row. If one
row was expected, there may be data errors such as duplicate rows, that are causing more than one row to be returned.
SQL9010 Escape 30 11/01/22 13:24:42.033086 QSQSCHEM
QSYS 18AF CLSH51 DILGARD
5770SS1 V7R3M0 160422 Job Log
DILGARD 11/01/22 13:25:52 EDT Page
Job name . . . . . . . . . . : CLSH51 User . . . . . . :
JEFF Number . . . . . . . . . . . : 336531
Job description . . . . . . : PGMJOBD Library . . . . . :
QGPL
MSGID TYPE SEV DATE TIME FROM PGM
LIBRARY INST TO PGM LIBRARY
To module . . . . . . . . . : CLSH51

To procedure . . . . . . . : CLSH51

Statement . . . . . . . . . : 15700

Message . . . . : RUNSQLSTM or
RUNSQL command failed.
Cause . . . . . : Errors were found
while processing the source for the
RUNSQLSTM command or the statement string for the RUNSQL
command. If a listing was generated, the
listing print file is QSYSPRT in
*LIBL. Recovery
. . . : Review the messages on the
listing or in the joblog. Either
correct the problems or specify a higher severity on the ERRLVL
parameter. Try the request again. Technical description . . . . . . . . :
The RUNSQLSTM or RUNSQL command found errors with a
severity greater than the
requested ERRLVL.

The QSYSPRT listing has:

5770SS1 V7R3M0 160422 Run SQL Statements SH51
Source file...............DILGARD/QSQLSRC
Member....................SH51
Target release............V7R3M0
Commit....................*NONE
Naming....................*SYS
Generation level..........10
Date format...............*JOB
Date separator............*JOB
Time format...............*HMS
Time separator ...........*JOB
Right margin..............80
Default collection........*NONE
IBM SQL flagging..........*NOFLAG
ANS flagging..............*NONE
Decimal point.............*JOB
Sort sequence.............*JOB
Language ID...............*JOB
Printer file..............*LIBL/QSYSPRT
Source file CCSID.........37
Job CCSID.................37
Statement processing......*RUN
Allow copy of data........*OPTIMIZE
Allow blocking............*ALLREAD
SQL rules.................*DB2
Decimal result options:
Maximum precision.......31
Maximum scale...........31
Minimum divide scale....0
Concurrent access
resolution..............*DFT
System time sensitive.....*YES
Source member changed on 10/10/11 11:26:48
5770SS1 V7R3M0 160422 Run SQL Statements SH51
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ..
1 UPDATE EQSLSALL
2 SET EQSLSALL.SLSNR = (SELECT ALCUSMST.SLSNR
3 FROM ALCUSMST
4 WHERE EQSLSALL.CUSNR = ALCUSMST.CUSNR

5 );

6

* * * * * E N D O F S O U R C E * * *
* *
5770SS1 V7R3M0 160422 Run SQL Statements SH51

Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
...+... 7 ...+... 8 SEQNBR
MSG ID SEV RECORD TEXT

SQL0088 0 1 Position 1 UPDATE applies to entire table.

SQL0811 30 1 Position 1 Result of SELECT more than one row.

Message Summary

Total Info Warning Error Severe Terminal

2 1 0 0 1 0

30 level severity errors found in source

* * * * * E N D O F L I S T I N G * * *
* *

I've never messed with ERRORLVL.

If I have the CL skip over the RUNSQLSTM, it works just fine.

Any idea what is the problem?

Thanks.