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