Number of records retrieved is not correct

[arrow483]

Need SQL experts. v7.2

Getting this when I try to refresh the MQT.

Number of records retrieved is not correct.
Result of SELECT more than one row.
Result of SELECT more than one row. Number of records retrieved is not correct.
Result of SELECT more than one row.
Result of SELECT more than one row.

This MQT has worked perfectly for years. Then it started throwing this error ONLY when run in Batch. Today it started throwing the error ALL the time. I know it could be a data issue, but I can't figure how ANY of these selects could cause this error. Suggestions on where to look?
Below code is a snip of EVERY line that has any kind of SELECT statement. Shouldn't the sum() and max() handle any possible duplicates?

CREATE TABLE GPSVIEWS/MBC6REPV1F AS (SELECT
then coalesce((select max(MM.ODUDT) from MOMAST MM
,coalesce((select sum(CNAMQT) from MBCNREPALL where T5.ADAENB=CNAENB AND
coalesce((select max(CNKZNB) from MBCNREPALL where T5.ADAENB=CNAENB AND
(Select coalesce(sum(MM.ORQTY+MM.QTDEV-MM.QTSCP-MM.QTYRC),0)
(Select coalesce(sum(PO.QTYOR+PO.QTDEV-PO.QTSCP-PO.STKQT),0)
(select coalesce(sum(MOHTQ),0) from ITEMBL IT2 where IT2.ITNBR = T2.CDAITX
coalesce((select max(CKCZCD) from MBCKREP where CKAENB = T2.CDAENB and
(select max(CKCZCD) from MBCKREP where CKAENB = T2.CDAENB and
(Select sum(TL.OTOrdTotal) from OrderTotal TL where T2.CDDCCD = '1'
When (Select sum(TL.OTOrdTotal) from OrderTotal TL where T2.CDDCCD = '1'
When (Select sum(TL.OTOrdTotal) from OrderTotal TL where T2.CDDCCD = '1'
When (Select sum(TL.OTOrdTotal) from OrderTotal TL where T2.CDDCCD = '1'
When (Select sum(TL.OTOrdTotal) from OrderTotal TL where T2.CDDCCD = '1'
When (Select sum(TL.OTOrdTotal) from OrderTotal TL where T2.CDDCCD = '1'
When (Select sum(TL.OTOrdTotal) from OrderTotal TL where T2.CDDCCD = '1'
When (Select sum(TL.OTOrdTotal) from OrderTotal TL where T2.CDDCCD = '1'
When (Select sum(TL.OTOrdTotal) from OrderTotal TL where T2.CDDCCD = '1'
When (Select sum(TL.OTOrdTotal) from OrderTotal TL where T2.CDDCCD = '1'

[arrow483]

Well, I tried to find it by commenting out one column at a time. Now I have zero subselects in my query. Still get the same error.

[Vectorspace]

That code does not make sense. It does not look like a complete SQL statement, but rather a collection of individual lines from multiple SQL statements.

"Result of SELECT more than one row" means that an SQL statement was run by the job that was expected to return no more than one row:
E.g. a non-cursor "select into" into host variables
E.g. a subselect expected to return a single value like ... "when (select x from y) = 1"

It is an annoying error to track down in a job that runs multiple SQL statements in multiple programs because the IBMi does not tell you where it happened.

For finding it, all I can suggest is add error logging to all SQL statements and/or run it in debug.

For resolving it, it's hard to advise without seeing the whole SQL statement.

You are correct that aggregate functions like sum() and max() do turn multiple rows into a single row, but only within a grouping. So if you are using a "group by" clause in that statement it may will return multiple rows.

If your select with aggregation is used as a subselect, or is then joined with another table or subselect, then you could still get multiple rows in the outer statement unless that too applies aggregation without grouping.

Finally, you can add "limit 1" to the end of a select statement which means it will return only the first row matching, rather than all rows. But while that would stop the error, it would not solve the underlying problem that is causing your query to return multiple rows. If it is returning multiple rows but shouldn't be, then something is wrong with your SQL which means even if you ignore all but the first row, that row's data may not be correct.

[arrow483]

Thanks for trying to help. To clarify, this is one SQL statement that is over 1,000 lines long and joins over a dozen files. And you are correct, I just included lines that have a SELECT/sub-SELECT in them. I do understand the error, I just don't understand why it happens here all of a sudden. It is correct that some selects would include multiple records, thus the need for MAX or SUM. The other strange thing is is happens ONLY if run in batch. Running the refresh in batch works correctly. But I can't keep getting up each night to make it run.

Thanks for the suggestion about other files. Though I usually always query physical files, I do on occasion I have a need to query a logical or view. Maybe one of those has the real error? I will have to investigate that.

[arrow483]

Finally solved the problem. Actually it was to an error, not in this SQL but in a view that was being used by this SQL. What a job to find it. Never able to figure out why it worked in interactive but failed in batch.

[Vectorspace]

Glad to hear it