At 03:01 PM 3/03/2005 +1300, Rob Martin wrote:

>Hi
>
>I am a stored procedure novice (in the extreme). I have the following
>SP that causes the following error
>
>ISC ERROR CODE 335544343 error
>'invalid request BLR at offset 6568
>bad parameter number'

This exception reflects wrong syntax in the inner SELECT INTO
query. Output arguments are variables inside the procedure. The
unbreakable rule with PSQL is that variables (other than context variables)
must be prefixed with a colon when referred to in a DSQL statement. (You
got it right in the outer FOR SELECT)

And -OH!! - beware of cursor scrambling. You are referring to an open
cursor on a DISTINCT set to try to do row-by-row operations. See
recommendations inline.

>CREATE PROCEDURE BackOrderedItems (DrTransRef Numeric(11) )
>RETURNS (
> ClOrderRef Numeric(11),
> OrderDate Date,
> CliOrderNo VarChar(16),
> ClientCode VarChar(20),
> ClientName VarChar(40),
> SellPrice NUMERIC(13,2),
> ItemCode VarChar(20),
> MakeName VarChar(30),
> Price Numeric(13,2),
> Quantity Numeric(13,2),
> Descriptn VarChar(254),
> Avail Numeric(13,2),
> Due Date)
>AS
> DECLARE VARIABLE i_ClOrderRef NUMERIC(11);
> DECLARE VARIABLE i_ItemRef NUMERIC(11);
>BEGIN
> FOR
> SELECT DISTINCT cs.ClOrderRef
> FROM DebtorInvoiceLine d
> JOIN ClientStock cs ON cs.CliStkRef = d.CliStkRef
> WHERE d.DrTransRef = :DrTransRef
> AND d.Subrank = 999
> AND ClOrderRef IS NOT NULL
> INTO i_ClOrderRef
> DO

BEGIN /* !!!!!!!!!!!!!!!!!!!!! */


/* use a different cursor alias for ClientStock in this query */

> SELECT co.ClOrderRef, CS1.ItemRef, co.OrderDate, co.CliOrderNo,
>c.ClientCode, ClientName, s.SellPrice, s.ItemCode, m.Descriptn, CS1.
>Sellprice, cs.Quantity, CS1.Descriptn
> FROM ClientStock CS1
> JOIN ClientOrders co ON co.ClOrderRef =cs.ClOrderRef
> JOIN Client c ON c.EntityRef = co.EntityRef
> JOIN Stock s ON s.ItemRef = cs.ItemRef
> JOIN Item i ON i.ItemRef = cs.ItemRef
> LEFT JOIN Make m ON m.MakeRef = i.MakeRef
> WHERE CS1.ClOrderRef = :i_ClOrderRef
> AND CS1.Subrank = 999
> AND CS1.PSlipRef IS NULL
> AND CS1.TransRef IS NULL
> AND (CS1.ActionType IS NULL OR CS1.ActionType = '')

prefix those variables with colons --->

> INTO :ClOrderRef, :i_ItemRef, :OrderDate, :CliOrderNo, :ClientCode,
>:ClientName, :SellPrice, :ItemCode, :MakeName, :Price, :Quantity, :Descriptn;
>
> SELECT sum(sl.shelfqty), min(spl.PREARRDATE)
> FROM StockLevel sl
> LEFT JOIN SupplyLine Spl on sl.itemref = spl.itemref
> WHERE sl.Itemref = :i_ItemRef

--- and here --->

> INTO :Avail, Due;
>
> SUSPEND;

END /* !!!!!!!!!!!!!!!!!!!!! */

>END

As a matter of curiosity, why are you declaring variables with an unscaled
NUMERIC? If it's to save memory, forget it. NUMERIC(11) will be
materialised as 8 bits, regardless, since its scale is larger than 9.

./hb