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'


Any suggestions ?




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
SELECT co.ClOrderRef, cs.ItemRef, co.OrderDate, co.CliOrderNo,
c.ClientCode, ClientName, s.SellPrice, s.ItemCode, m.Descriptn, cs.
Sellprice, cs.Quantity, cs.Descriptn
FROM ClientStock cs
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 cs.ClOrderRef = :i_ClOrderRef
AND cs.Subrank = 999
AND cs.PSlipRef IS NULL
AND cs.TransRef IS NULL
AND (cs.ActionType IS NULL OR cs.ActionType = '')
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
INTO Avail, Due;

SUSPEND;
END



--
Rob Martin
Software Engineer

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

Wild Software Ltd