I'm passing a single field from a single row into a variable in a procedure. The following example works if MSEG query results in a single row;
EXAMPLE 1: lets say I have a one row in MSEG table;
Row1 MENGE: 100
select a.MENGE into OnHandQty from "MSEG" where condition = X
Result is OnHandQty = 100 << SUCCESS!!
EXAMPLE 2: lets say I have two rows in mseg table;
Row1 MENGE 100
Row2 MENGE 50
To handle multiple rows in the query I thought the following would work;
select sum(a.MENGE) into OnHandQty from "MSEG" where condition = X
I get error 'fetch returns more than requested number of rows'. Although if I run the SQL without attempting to insert into a variable I can clearly see the result is 1 single row result with value of 150.
Is aggregation not supported when using INSERT INTO VARIABLE in a procedure?