"fabiano_bonin" <fabiano@...> wrote:

>
> -- If i try to select INNER JOINING the procedure,
> -- i have an error.
>
> select
> a.*
> from
> test a
> inner join sp_test(a.test_id) b on 1 = 1
>
> -- Statement failed, SQLCODE = -508
> -- no current record for fetch operation

Known (and very old) bug. The optimizer doesn't understand that procedures
may depend on other streams via input parameters and hence it places the
procedure at the beginning of a join when stream "A" is inactive to be
fetched from yet.

> -- If i try to select LEFT JOINING the procedure,
> -- it works and it seems the procedure is evaluated one time for each
> row of the 'test' table.
>
> select
> *
> from
> test a
> left join sp_test(a.test_id) b on 1 = 1

This is correct and "as designed". By using LEFT JOIN you just force your
own join order and hence you're able to workaround the bug.


Dmitry