Hi All

Some may remember my SP that I had problems with. I now have it running
in the following format.


CREATE PROCEDURE pr_BackOrderedItems (DrTransRef Numeric(11))
RETURNS (
TransNum Numeric(11),
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(15,4),
Descriptn VarChar(254),
Avail Integer,
Due Date)
AS
DECLARE VARIABLE i_ClOrderRef Numeric(11);
DECLARE VARIABLE i_TransNum Numeric(11);
DECLARE VARIABLE i_ItemRef Numeric(11);
BEGIN
FOR
SELECT DISTINCT cs.ClOrderRef, dt.TransNum
FROM DebtorInvoiceLine d
JOIN DebtorTrans dt ON dt.DrTransRef = d.DrTransRef
JOIN ClientStock cs ON cs.CliStkRef = d.CliStkRef
WHERE d.DrTransRef = :DrTransRef
AND d.Subrank = 999
AND ClOrderRef IS NOT NULL
INTO i_ClOrderRef, i_TransNum
DO
BEGIN
SELECT :i_TransNum, co.ClOrderRef, cs2.ItemRef,
co.OrderDate, co.CliOrderNo, c.ClientCode, ClientName, s.SellPrice,
s.ItemCode, m.Descriptn, cs2. Sellprice, cs2.Quantity, cs2.Descriptn
FROM ClientStock cs2
JOIN ClientOrders co ON co.ClOrderRef =cs2.ClOrderRef
JOIN Client c ON c.EntityRef = co.EntityRef
JOIN Stock s ON s.ItemRef = cs2.ItemRef
JOIN Item i ON i.ItemRef = cs2.ItemRef
LEFT JOIN Make m ON m.MakeRef = i.MakeRef
WHERE cs2.ClOrderRef = :i_ClOrderRef
AND cs2.Subrank = 999
AND cs2.PSlipRef IS NULL
AND cs2.TransRef IS NULL
AND (cs2.ActionType IS NULL OR cs2.ActionType = '')
INTO :TransNum, :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
END


The first part builds a list of orders referenced in this invoice. The
second part lists any unsupplied items in those orders. My question is,
how do I return no results when no outstanding items exist? Currently I
return a Null record.

--
Rob Martin
Software Engineer

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

Wild Software Ltd