Result of SELECT more than one row.

[ARobb73]

I've exhausted my limited knowledge of embedded SQL, so I'm hoping someone here can shed some light on what I'm doing wrong.

I have a couple of programs that are running that are kicking out 600 page job logs that I can't seem to get to the bottom of. The messages for both are the same: Result of SELECT more than one row.

The following statement is the statement in the program that is causing the error:

exec sql select count(cnote) into :countx from dliiqante where aipart = :hld_itm and aiserl = :rserno and cnote like 'Incorrect%';

It's intent is to count the number of records from our inspection notes file that have the word 'Incorrect' in them. There's probably a better way to do this, but from everything I can see it's working correctly.

I'm including some sample data so y'all can see what the file looks like:

AIPART              AISERL             AIBADG AIDATE     AITIME BTABID  BTITLE    BINSSEQ    BQUESTID    CNOTE
9999-743-001   W1.21056.059   25500     20210302   73655      1                NOTE        1                   131214              NoteHeader -------------------------------------- IQ1 03/02/2021 07:36:55
9999-743-001   W1.21056.059   25500     20210302   73655      1                NOTE        2                   131214              CmpltNote Station 2 criteria passed
9999-743-001   W1.21056.059   25500     20210302   73721      2                NOTE        3                    131216             NoteHeader -------------------------------------- IQ1 03/02/2021 07:37:21
9999-743-001   W1.21056.059   25500     20210302   73721      2                NOTE        4                     131216             CmpltNote High Potential criteria passed
9999-743-001   W1.21056.059   25500     20210302   73740     3                NOTE        5                     131225             NoteHeader ------------------------------------- IQ1 03/02/2021 07:37:40
9999-743-001   W1.21056.059   25500     20210302   73740     3                NOTE        6                     131225             CmpltNote Wet Test 1 criteria passed
9999-743-001   W1.21056.059   25500     20210302   73756     4                NOTE        7                      131233             NoteHeader ------------------------------------- IQ1 03/02/2021 07:37:56
9999-743-001   W1.21056.059   25500     20210302   73756     4                NOTE        8                     131233             CmpltNote Wet Test 2 criteria passed
9999-743-001   W1.21056.059   25500     20210302   73827     5                NOTE        9                     131239             NoteHeader ------------------------------------- IQ1 03/02/2021 07:38:27
9999-743-001   W1.21056.059   25500     20210302   73827     5                NOTE      10                     131239             CmpltNote Crate criteria passed
9999-743-001   W1.21056.060   25500     20210302   74026    1                NOTE        1                      131214             NoteHeader ------------------------------------- IQ1 03/02/2021 07:40:26
9999-743-001   W1.21056.060   25500     20210302   74026    1                NOTE        2                      131214             CmpltNote Station 2 criteria passed
9999-743-001   W1.21056.060   25500     20210302   74052    2                NOTE        3                      131216             NoteHeader ------------------------------------- IQ1 03/02/2021 07:40:52
9999-743-001   W1.21056.060   25500     20210302   74052    2                NOTE        4                      131216             CmpltNote High Potential criteria passed
9999-743-001   W1.21056.060   25500     20210302   74117     3                NOTE        5                       131225             NoteHeader ------------------------------------ IQ1 03/02/2021 07:41:17
9999-743-001   W1.21056.060   25500     20210302   74117     3                NOTE        6                       131225            CmpltNote Wet Test 1 criteria passed
9999-743-001   W1.21056.060   25500     20210302   74131     4                NOTE        7                       131233             NoteHeader ------------------------------------- IQ1 03/02/2021 07:41:31
9999-743-001   W1.21056.060   25500     20210302   74131     4                NOTE        8                       131233             CmpltNote Wet Test 2 criteria passed
9999-743-001   W1.21056.060   25500     20210302   74202    5                NOTE        9                       131248             NoteHeader -------------------------------------- IQ1 03/02/2021 07:42:02
9999-743-001   W1.21056.060   25500     20210302   74202    5                NOTE      10                       131248             Incorrect answer on station Crate 131248 BACK OF MACHINE
9999-743-001   W1.21056.060   25500     20210302   74202    5                NOTE      11                        131249             Incorrect answer on station Crate 131249 FRONT OF MACHINE
9999-743-001   W1.21056.060   25500     20210302   74217     5                NOTE      12                        131239            NoteHeader -------------------------------------- IQ1 03/02/2021 07:42:17
9999-743-001   W1.21056.060   25500     20210302   74217     5                NOTE      13                        131239            CmpltNote Crate criteria passed

So what we have here are two machines, both the 9999-743-001 model one with a serial number ending in .059 and the other in .060
.059 passed all of it's tests, while .060 failed on 2 tests on the wet test 2 station.

The code I posted is meant to count the records with 'Incorrect' in them. It does work properly, but dumps the message: "Result of SELECT more than one row."

Thanks for any suggestions.

Andy

[Vectorspace]

I cannot see a reason why that SQL statement would return more than one row. It's selecting only the aggregate function COUNT(), with no grouping or join. By definition it should only be one row. How are you certain that it's this statement? The job log is not good at revealing the actual locations of embedded SQL errors.

When you run the statement manually does it return multiple rows?

Can you modify the program so it checks the result of the exec sql (by checking SQLSTT, SQLCOD, or using the SQL GET_DIAGNOSTICS) to retrieve and log the result somewhere so you can confirm it is this statement?

Can you temporarily Add "limit 1" to the end of the statement, run the program, and check the results?

exec sql
  select count(cnote)
    into :countx
    from dliiqante
   where aipart = :hld_itm and aiserl = :rserno and cnote like 'Incorrect%'
   limit 1;

This will force a statement that would return multiple rows, to return only the first row. If it makes the errors go away it means this statement is the issue.
***THIS IS NOT A FIX***. if it is returning multiple rows then something is fundamentally wrong so all the results are suspect, so ignoring the extra rows is not the answer