Many thanks to Lucas and Martijn!!

I removed the Suspend and the procedure works well.

set term #;
create procedure sp_end_of_day_2
returns (
prod_code integer,
vqty float
)
as
declare variable v_qty float;
declare variable v_sum_qty float;
begin
for select prod_code, qty
from tm_pos_product
into :prod_code, :v_qty
do
begin
select sum(t_qty)
from t_transaction
where t_prod_code = :prod_code
into :v_sum_qty;

if (v_sum_qty is null) then v_sum_qty = 0;

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;#

again, thanks a lot!

Regina Phandu

----- Original Message -----
From: "Lucas Franzen" <luc@...>
To: <ib-support@yahoogroups.com>
Sent: Thursday, October 03, 2002 3:28 AM
Subject: Re: [ib-support] cursors in sp??


>
> Regina,
>
>
> Regina Phandu schrieb:
> >
> > Lucas,
> >
> > the sp that you gave me works well, but it only works for 1 record. what
i
> > need is a cursors, i believe.
>
> How do you get the results?
> Since there is a SUSPEND in the procedure it should work for <n>
> records.
>
> > and i also need to update the quantity of the product. not only returns
> > values/display the values.
>
> You can do this within the procedure, too.
>
> SELECT SUM ( QTY ) FROM TRANSAKTION
> WHERE PROD_CODE = :PROD_CODE INTO :SUMQTY;
>
> IF ( SUMQTY IS NULL ) THEN SUMQTY = 0;
>
> QTY = PRODQTY - SUMQTY;
>
> /* =============== */
> /* UPDATE / DELETE */
> /* =============== */
> IF ( SUMQTY > 0 ) THEN
> BEGIN
> UPDATE PRODUCT SET
> PROD_QTY = :SUMQTY
> WHERE PROD_CODE = :PROD_CODE;
>
> DELETE FROM TRANSACTION WHERE PROD_CODE = :PROD_CODE;
> END
>
> SUSPEND;
>
> Since you want to do an update it might be a better idea just to sum the
> transaction table, update the appropriate entries in product and select
> * from product (since irt has the new sum in the field QTY then).
>
> You can do the summing on transaction like:
> FOR SELECT SUM ( QTY ), PROD_CODE from TRANSACTION
> GROUP BY PROD_CODE
> ...
>
> then.
>
>
> > so, i guess, i need cursors and updating the
> > product qty itself.
> > I tried so hard with cursors, but i still can't make it work.
>
> See Helen's answer.
>
> Luc.
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>