RE: How do I monitor for the message "Indicator variable required" in the following statement?

The placement of the Coalesce/IFNULL is important here.

The SQL message is indicating that at some point the query received a null column value.

If you wrap the Sum with the coalesce (i.e. Coalesce(Sum(f1+f2+f3....),0) ) then you are comparing the "final" value of the ROW to null and then replacing that with the zero from the coalesce.

This will omit the *row* that caused the original query output to be null.

So the question I would ask is when a row contains 8 columns and only 1 column is null, should that entire row be omitted from the summation?

If you don't want to omit the row, then you would need to coalesce each null capable field inside the summation.

Select sum( coalesce( WBBLT1 ,0) + coalesce( WBBLT2 ,0) + coalesce( wbblt3 ,0) + coalesce( wbblt4 ,0) - coalesce( wbaqmf ,0) - coalesce( wbrsoq ,0) - coalesce( wbbosq ,0) - coalesce( wbusoq,0) )


For example. Here I build a simple cte with two columns (rx, ry).
I have three rows.
10, 1
20, 2
30, null


This summation returns 33, which means it doesn't see the 3rd row at all.
select sum(rx+ry) from (
values (10,1), (20,2), (30,(nullif(3,3)))
) t1 (rx, ry) ;


Here I specifically select the 3rd row. This summation returns null.
This is basically what your query is getting because you are applying a where clause.

select sum(rx+ry) from (values (10,1), (20,2), (30,(nullif(3,3))) ) t1 (rx, ry) where rx = 30;


Here I wrap the Sum in a Coalesce. This returns 33.

select coalesce( sum(rx+ry) ,0) from (values (10,1), (20,2), (30,(nullif(3,3))) ) t1 (rx, ry) ;


Here I wrap the Sum in Coalesce again, but add the where clause. This returns zero.

select coalesce( sum(rx+ry) ,0 ) from (values (10,1), (20,2), (30,(nullif(3,3))) ) t1 (rx, ry) where rx = 30;



This returns 63. Moving the Coalesce into the Sum gets a different result because it allows the Sum to include the rx column from the 3rd row.

select sum( coalesce( rx,0) + coalesce( ry ,0) ) from (values (10,1), (20,2), (30,(nullif(3,3))) ) t1 (rx, ry) ;


Here is the sample with the where clause. This returns 30.

select sum( coalesce( rx,0) + coalesce( ry ,0) ) from ( values (10,1), (20,2), (30,(nullif(3,3))) ) t1 (rx, ry) where rx = 30;



If you need to include the row and just omit the null column, then you need to individually coalesce each column that can be null.



Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of dfreinkel
Sent: Tuesday, February 4, 2020 1:46 PM
To: RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: How do I monitor for the message "Indicator variable required" in the following statement?

exec sql Select sum(WBBLT1 + WBBLT2 + wbblt3 + wbblt4 -
wbaqmf - wbrsoq - wbbosq - wbusoq)
into :wQty_on_Hand
from IC140M
where WBPPN = :slptno
and wbco = :slco
and wbwhno = :slloc;

In sql scripts, I have used IFNULL, however, IFNULL is not usable in a RPG program.

This is a 7.2 system.
TIA
Darryl Freinkel