Thanks Helen and Adam

Sorry I have probably confused the issue, I should have used a real
example.

However what I really wanted to say was we have lots of different SQLs
that JOIN the StockLevel table to get quantities. The Join has always
been something like

JOIN Stocklevel sl ON (sl.ItemRef = OtherTable.ItemRef AND sl.RegionRef
= :RegionRef)

Where sl.ItemRef links to another table in the select. RegionRef
usually is a parameter but sometimes also comes from another table.

This worked fine as Stocklevel would always return 1 and only 1 record.
I know I can group but for some of our SQLs this will probably not be
possible. I wanted to JOIN my stored procedure instead but I have seen
Helen warn against it a number of times (always recommending the
creation of a stored procedure instead). However I don't want to have
to do that for a large number of SQLs. Thus my comments about not sure
if my SP helps :)

If I wanted to join my Stored procedure in some cases I thought I could
use the following style join...

JOIN pr_StockLevelByRegion(OtherTable.ItemRef, :RegionRef) ON 1 = 1

(however this gives me a 'no current record for fetch operation' when I
try it)


I know FB 2 supports Selects from selects (sorry forgot the techie
name). This might help in cases where the primary table is StockLevel
but in other cases I would need to JOIN a SELECT, but you cant do that
(Correct ?).

Sorry I hope the above clears things up a little. Sorry If I am
confusing but I have been struggling to rewrite some SQLs all morning
and am starting to loose the plot :)

Rob Martin
Software Engineer

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

Wild Software Ltd