Hi,

>>> As I understand, with inner joins FB for some reasons puts the
>>> stored procedure at the first place (as a left dataset), and then
>>> joins the rest tables with it. Since the stored procedure uses
>>> fields from the "following" tables, it doesn't "know" the value of
>>> those fields when executing. Outer join "forces" the join order.

<snip>

> Originally in my case, it was stuff like
> ...from Rooms r
> join GetRmCosts(r.ID, :UID, :DoAll) rmc on 1=1...
> and GetRmCosts didn't return the value of r.ID as its output
> parameter. I rewrote the SP, so it returns ID now; and the stuff like
> ...from Rooms r
> join GetRmCosts(r.ID, :UID, :DoAll) rmc on rmc.RmID=r.ID+0...
> doesn't work for some reasons either.

<snip>

> Left outer join works quite well for me. But it's a workaround that
> took some time to find out (and the strange error message messed
> things up a little).
> I don't know about Christian's case (he is the original poster).

I'll try to describe it:
Stored Procedures/Aggregate streams/UNION streams/sorted streams are put on the top site of the
"execution PLAN" inside an INNER JOIN section. Regardless if there's any dependency with an other
stream.
In the above example the procedure GetRmCosts() depends on Rooms due the r.ID given as parameter,
but the optimizer will always process SP first (per INNER JOINed block). When processing the SP the
Rooms stream isn't available yet and you get an "no current record for fetch operation" error.
Using a LEFT JOIN forces the order of execution and this is the only good workaround at the moment.

SELECT
*
FROM
MyTable t1
LEFT JOIN StoredProcedure(t1.ID) ON (1 = 1)

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info