Regina,
At 06:16 PM 03-10-02 -0700, you wrote:

>I tested the
>procedure and it works just like i need it.
>
>set term #;
>create procedure sp_end_of_day_2
>
>as
> declare variable v_qty float;
> declare variable v_sum_qty float;
> declare variable vqty float;
> declare variable prod_code integer;
>begin
> for select prod_code, qty
> from tm_pos_product
> into :prod_code, :v_qty
> do
> begin
> select sum(t_qty) <---- A
> from t_transaction
> where t_prod_code = :prod_code
> into :v_sum_qty;
>
> if (v_sum_qty is null) then v_sum_qty = 0; <---- B
>
> vqty = v_qty - v_sum_qty;
>
> if (v_sum_qty > 0) then
> begin
> update tm_pos_product set qty = :vqty
> where prod_code = :prod_code;
> end
>
> end
>end#
>set term;#

Watch out for nulls in aggregates! Did you know that, if there is even
just ONE null value in that group of records at A, then that v_sum_qty will
return as null? I suspect this is not what you want: I think you really
want null to be counted as zero during the sum() calculation.

To do this, you can use the invl() function in the fbudf.dll UDF library.
HOWEVER, because you have stored your values as float - they will need to
be cast to numeric in order for invl() to work on them.

You have to declare the UDF to your database - see the Language Reference
for the syntax.

You would then change that block of code to something like:

declare variable v_sum_qty numeric(15,3);
...
do
begin
select sum(invl( CAST (t_qty AS NUMERIC(15,3) ), 0.000))
from t_transaction
where t_prod_code = :prod_code
into :v_sum_qty;

if (v_sum_qty is null) then v_sum_qty = 0.000;
vqty = v_qty - CAST (v_sum_qty AS FLOAT);
...

Perhaps a simpler way to do it (without casting and re-casting) would be this:
...
declare variable tr_qty float;
...
do
begin
v_sum_qty = 0;

for select t_qty
from t_transaction
where t_prod_code = :prod_code
into :tr_qty do
begin
if tr_qty is not null then
v_sum_qty = v_sum_qty + tr_qty;
end
vqty = v_qty - v_sum_qty;
...

HTH

heLen