At 01:18 AM 24/08/2006, you 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?

First, if you are going to try to join to a SP (a bad idea) you have
to join to an OUTPUT field. Think about it. :-) How can the engine
join your table stream to a stream that doesn't exist?

Secondly, if you write a SP that you want to be able to join with
other sets, then do a rethink and instead write your SP to take the
join criteria as input and use it (them) to construct and return the
required set directly. The same goes if you want the output ordered
or grouped - do it inside the SP.

Joining to a selectable SP is one of those things that falls into the
category of "Just because you can, doesn't mean you should". If you
want a special set to which you can join, use a view.

./heLen