--- In firebird-support@yahoogroups.com, "Paul R. Gardner"
<gardnerp@...> wrote:

>
> I'm trying to join a table to a stored procedure without any luck. The
> procedure I'm joining to has only one input parameter. Here's a
> simplified version of the SQL:
>
> select invoice.i_number, some_stored_procedure.some_field
> from invoice
> inner join some_stored_procedure(invoice.i_number) on
> some_stored_procedure.input_field = invoice.i_number
>
> It always has an issue with the join. It says 'column unknown
> some_stored_procedure.input_field'. Next I tried:
>
> select invoice.i_number, some_stored_procedure.some_field
> from invoice
> inner join some_stored_procedure(invoice.i_number) on 1 = 1
>
> This eliminated the issue I was having with the join. I know 1 always =
> 1, but I had already narrowed this to a 1 to 1 correlation by passing in
> the parameter in the first place. The new error I got was:
>
> The cursor identified in the update or delete statement is not
> positioned on a row.
> no current record for fetch operation.
>
> Any ideas?

The optimiser does not recognise that it needs to run through invoice
before it has enough information to call the stored procedure.
Hopefully that will be improved in future versions, but for now you
need to force it to use invoice first, so you will need to change your
inner join to a left join. To compensate for the fact you are now
using an outer join, you will need to include a where condition to
eliminate such rows (eg. where some_stored_procedure.some_field is not
null)

Adam