|
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.
--
*Jeff Crosby*
Senior Vice President
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
direct.dilgardfoods.com
The opinions expressed are my own and not necessarily the opinion of my
company. Unless I say so.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
