Hi All,

I have below S.Proc
Eventhough the table has more than 1 record
below  S.procedure updates only 1 record.
Could  someone please tell me what is wrong.

Thank you for your time.

ALTER PROCEDURE LASTPURCHASEINFO2
(
  "RNUM" INTEGER
)
RETURNS
(
  "RNO" INTEGER,
  "TNAME" VARCHAR(5),
  "LASTTABLE" VARCHAR(12),
  "TDATE" DATE,
  "CUSTNO" INTEGER,
  "LASTPRICE" NUMERIC(18, 4),
  "AVGCOST" NUMERIC(18, 4),
  "STOCK" NUMERIC(18, 2),
  "ITEMNO" VARCHAR(20)
)
AS
BEGIN
  FOR
    SELECT ITEMNO, SUM(NETPRICE * QTY)/SUM(QTY)AVGCOST, SUM(TRS+INCOME-OUTGO)STOCK
    FROM INVDETAIL
    WHERE TNAME IN ('I', 'S', 'M') and
    ITEMNO IN (SELECT ITEMNO FROM INVDETAIL WHERE RNO=:"RNUM")
    GROUP BY ITEMNO
    INTO :"ITEMNO", :"AVGCOST", :"STOCK"
 
 DO
 BEGIN

    SELECT I.RNO,I.TNAME,I.TDATE,I.CUSTNO ,ID.NETPRICE AS LASTPRICE
    FROM INVOICE I
    JOIN INVDETAIL ID ON I.RNO=ID.RNO
    WHERE ITEMNO=:"ITEMNO"
    and I.TDATE=(SELECT MAX(I2.TDATE) FROM INVOICE I2 JOIN INVDETAIL ID2
    ON I2.RNO=ID2.RNO  WHERE I2.TNAME IN ('I', 'S', 'M'))
    INTO :"RNO",:"TNAME", :"TDATE",:"CUSTNO",:"LASTPRICE";

    IF (TNAME='I') THEN LASTTABLE='Invoice';
    IF (TNAME='S') THEN LASTTABLE='Stocks';
    IF (TNAME='M') THEN LASTTABLE='Manx';

    UPDATE ITEMS SET LASTRNO=:"RNO", LASTCUSTNO=:"CUSTNO", LASTDATE=:"TDATE",
    LASTPRICE=:"LASTPRICE", LASTTABLE=:"LASTTABLE", AVGCOST=:"AVGCOST", STOCK=:"STOCK"
    WHERE ITEMNO= : ITEMNO;

  SUSPEND;
  END
END