Svein, Thank you so very much for your detailed reply.
________________________________ Kimden: Svein Erling Tysvær <[email protected]> Kime: "[email protected]" <[email protected]> Gönderildiği Tarih: 11 Eylül 2013 9:47 Çarşamba Konu: RE: [firebird-support] S.Proc >> The result should be like below >> CHILDITEMNO...QTY....AVGCOST.....LASTPRICE >> AA .............2.......1200..........1220 >> BB .............4........600...........630 > >If I got it right you should move the section where you get the LastPrice and >AvgCost to the loop. Something like: > >CREATE PROCEDURE BOMCOST ( > "ITEMNO" VARCHAR(20) ) >RETURNS >( "CHILDITEMNO" VARCHAR(20), > "QTY" NUMERIC(18, 2), > "LASTPRICE" NUMERIC(18, 4), > "AVGCOST" NUMERIC(18, 4)) >AS >BEGIN > for > SELECT B.CHILDITEMNO, B.QTY > FROM BOM B > WHERE B.ITEMNO= : ITEMNO > INTO :CHILDITEMNO, :QTY > DO > begin > /*First i'd like to collect LastPurchasePrice & AverageCost of the items > /*from ITEMSMOVE View. > SELECT SUM(NETPRICE * QTY)/SUM(QTY) FROM ITEMSMOVE >/* I think you missed this where) */ > WHERE ITEMNO = :CHILDITEMNO > INTO : AVGCOST; > > /*Then i'd like get the last purchase price from the upper same View*/ > SELECT UPRICE > FROM ITEMSMOVE > WHERE (ITEMNO=:ITEMNO) and > TDATETIME=(SELECT MAX(TDATETIME) FROM ITEMSMOVE > WHERE TNAME IN ('B','M', 'T' )) > INTO :LASTPRICE; > > SUSPEND; > end >END If items can be moved at different times, the last subselect probably also ought to be include ITEMNO to avoid lots of nulls (also adding another select since I stopped using aggregate subselects in the WHERE clause a few Firebird versions ago, since they at least used to be slow in older Firebird versions - actually, I would have simplified to using NOT EXISTS rather than a CTE if it can be guaranteed that there's no rows in ITEMSMOVE for the same ITEMNO with a later TDATETIME with a TNAME NOT IN ('B', 'M', 'T') that should be excluded): CREATE PROCEDURE BOMCOST ( "ITEMNO" VARCHAR(20) ) RETURNS ( "CHILDITEMNO" VARCHAR(20), "QTY" NUMERIC(18, 2), "LASTPRICE" NUMERIC(18, 4), "AVGCOST" NUMERIC(18, 4)) AS BEGIN FOR SELECT B.CHILDITEMNO, B.QTY FROM BOM B WHERE B.ITEMNO= :ITEMNO INTO :CHILDITEMNO, :QTY DO BEGIN /*Collect LastPurchasePrice & AverageCost of the items /*from ITEMSMOVE View. SELECT SUM(NETPRICE * QTY)/SUM(QTY) FROM ITEMSMOVE WHERE ITEMNO = :CHILDITEMNO INTO :AVGCOST; /*Then i'd like get the last purchase price from the upper same View*/ /*I changed from :ITEMNO to :CHILDITEMNO below since you otherwise would*/ /*get the same value for every returned row*/ WITH TMP (TDATETIME) AS (SELECT MAX(TDATETIME) FROM ITEMSMOVE WHERE ITEMNO = :CHILDITEMNO AND TNAME IN ('B','M', 'T') SELECT UPRICE FROM TMP T LEFT JOIN ITEMSMOVE ON T.TDATETIME = I.TDATETIME WHERE T.ITEMNO=:CHILDITEMNO INTO :LASTPRICE; SUSPEND; END END HTH, Set
