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.

That is correct.
Select will give you a value.

You need this type of construct, (very, very simplified) if you want to
return a number of records meeting your WHERE conditions, and you need
to put your own requirements into the main body of the SP.

CREATE PROCEDURE FIFOValuation

> ( tiItemID ID, tbCurrStock QTY )
> RETURNS
> ( tbValuation QTY,

myoutput1 Qty,
myoutput1 QTY )

> AS (... whatever you want as per your SP below ....)

begin
for select
mydata1, mydata2
from myjoined tables
where mycondition is satisfied
into
:myoutput1, :myoutput2
do suspend;
end

Alan J Davies
Aldis

On 05/03/2011 10:08, Venus Software Operations wrote:
> 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 ; ^
>
>
> ------------------------------------------------------------------------
>
> No virus found in this message.
> Checked by AVG - www.avg.com <http://www.avg.com>
> Version: 10.0.1204 / Virus Database: 1435/3482 - Release Date: 03/04/11
>