Thanks for you help Helen

I never realised I was causing a problem by using the same alias in a
following query.


Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



Helen Borrie wrote:

>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
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>

[Non-text portions of this message have been removed]