Hi all

In view of my recent thread I was wanting to convert a SQL suggested to
me by Jose into a SP. I tried the below code but I get an error that
SUSPEND is token unknown. I have tried to put and remove ; in the SQL
and the SUSPEND command, also the command itself, but no joy.

One more thing is that, this particular SP returns just one row of data
/ value. But there is also another that would returns multiple rows of
data what might be different in such a situation when writing this.

Please advise

Kind regards
Bhavbhuti

SET TERM ^ ;

CREATE PROCEDURE FIFOValuation
( tiItemID ID, tbCurrStock QTY )
RETURNS
( tbValuation QTY )
AS
--DECLARE VARIABLE variable_name < datatype>;
BEGIN
SELECT SUM(qty * bRate) AS bValuation
FROM (SELECT IIF(bQty + qtyOut >= :tbCurrStock /*current stock */
, (:tbCurrStock /*current stock */ - qtyOut)
, bQty) as qty
, bRate
FROM (SELECT sPurchaseBillItem.bQty
, sPurchaseBillItem.bRate
, COALESCE((SELECT SUM(PurchaseBillItemInner.bQty)
FROM sPurchaseBillItem AS
PurchaseBillItemInner
JOIN tPurchaseBill AS
PurchaseBillInner
ON
PurchaseBillInner.iID = PurchaseBillItemInner.iPID
WHERE (PurchaseBillInner.tDt >
tPurchaseBill.tDt AND PurchaseBillItemInner.iItemID = :tiItemID /* item
id */))
, 0) as qtyOut
FROM tPurchaseBill
JOIN sPurchaseBillItem
ON sPurchaseBillItem.iPID = tPurchaseBill.iID
AND sPurchaseBillItem.iItemID =
:tiItemID /* item id */) AS PurchaseBillsIntermediate
WHERE (qtyOut <= :tbCurrStock /*current stock */)) AS
PurchaseBillsFIFO;

SUSPEND;
END^

SET TERM ; ^