I have a view like below
ITEMNO INCOME OUTGO
AA 10 2
BB 15 0
...
so on
I'd like to update every record of the ITEMS table (item cards) according to above view's results.
I created a stored procedure like below
SET TERM ^ ;
ALTER PROCEDURE UPDATEITEMSQTY (
ACTIVEYEAR VARCHAR(4) )
AS
declare variable ITMNO VARCHAR(20);
declare variable INCOME NUMERIC(18, 2);
declare variable OUTGO NUMERIC(18, 2);
begin
FOR SELECT IM.ITEMNO,
COALESCE(SUM(IM.INCOME),0) INCOME,
COALESCE(SUM(IM.OUTGO),0) OUTGO
FROM ITEMMOVEMENTS IM /* View */
WHERE EXTRACT(YEAR FROM IM.TDATE )=:ACTIVEYEAR and
GROUP BY IM.ITEMNO
INTO :ITMNO,:INCOME,:OUTGO
DO
BEGIN
UPDATE ITEMS /* Item Cards Table */
SET INCOME=:INCOME, OUTGO=:OUTGO,
STOCK= :INCOME - :OUTGO
WHERE ITEMNO=:ITMNO;
END
SUSPEND;
End
^
SET TERM ; ^
Above stored procedure doesn't correctly update some records.