> I have stored procedure, returning only one row. Maybe more than one row.
> This procedure is selective, i.e. it has SUSPEND in it's body.
> I'm executing query:
>
> SELECT T.*, SP.*
> FROM TABLE T, PROC(T.ID) SP
>
> This shows error message:
> "no current record for fetch operation"
>
> But if I add WHERE clause
> where T.ID = SP.ID
>
> All will be fine.

The problem is caused by optimizer, because it can
freely decide which table in join evaluate first.
So in this simple statement:
SELECT T.*, SP.*
FROM TABLE T, PROC(T.ID) SP
it seems better (at least from optimizer's point of view)
to begin with second "table" (i.e. PROC(...)).
But then IB has no way how to supply parameter
to SP from table T !!

It can be considered as bug, because IB should either
-recognize that PROCedure needs parameter from Table
and change evaluation order accordingly, or
-refuse to prepare such statement at all,
because it is against SQL standard (I believe -
perhaps somebody will correct me):
SQL statement should behave as if its parts
are evaluated in certain fixed order, something like
-take all rows from all participating tables
-join them
-apply WHERE clause on the result
-etc...
But you can't get all rows in advance, because
select procedure PROC needs column
from table T as parameter.

Some ways how to make IB evaluate T first:

-use outer joins, like
SELECT T.*, SP.*
FROM TABLE T LEFT JOIN PROC(T.ID) SP ON 1=1

-use scalar subquery (if your procedure returns only 1 row)
SELECT T.*, (SELECT id FROM PROC(T.ID))
FROM T

-unfortunately it is not possible to specify PLANs,
I always get "BLR syntax error ..." when join contains SP.
-it is even not possible to print PLAN chosen by IB.

> Why engine can't fetch rows from SP in first case, and do it in second?
>
> I know that SELECT in the first case must return multiplication of T and SP
> records. But if SP returns only one row it can be very useful to write SPs
> to convert or manipulate data instead of creating UDF.

For one row use scalar subquery.

Ivan
prenosil@...