Hi Myles

I had not idea that was a restriction - one of the joys of being a
novice :) Anyway Apart from finding another fault with the SP (it would
fail for multi line results - Fixed), I think I have discovered how to
get 0 results when there are no results.

I added the following around my SUSPEND. This seems to work great,
however I may be breaking something else, but all seems good so far.

IF (:ClOrderRef IS NOT NULL) THEN
SUSPEND;
ELSE
EXIT;


FYI the full SP is shown below

CREATE PROCEDURE pr_BackOrderedItems (DrTransRef Numeric(11)) /* RM
04/03/2005 build a list of backordered items for a given invoice */
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

FOR
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
DO
BEGIN
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;

IF (:ClOrderRef IS NOT NULL) THEN
SUSPEND;
ELSE
EXIT;
END
END
END



Rob Martin
Software Engineer

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

Wild Software Ltd



Myles Wakeham wrote:

>I was under the impression that any SP that finds no results to return will
>return a NULL row. Is that not the case?
>
>Myles
>
>===========================
>Myles Wakeham
>Director of Engineering
>Tech Solutions Inc.
>Scottsdale, Arizona USA
>Phone (480) 451-7440
>Web: www.techsol.org
>
>
>
>
>
>
>
>
>
>
>