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.