--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
<iblist@...> wrote:

>
> reg_hill_labs wrote:
> > Hi,
> >
> > We currently have a stored procedure to do some stuff that can't be
> > done easily with SQL. This procedure takes one id parameter and
> > returns a number of different fields that you can use by calling the
> > procedure in an SQL Statement.
> >
> > However, a single SQL statement that uses this procedure might use it
> > multiple times to use the different fields the procedure returns.
> > This seems quite a waste of resources as we are running exactly the
> > same procedure multiple times to get the results.
> >
> > Here is an example SQL statement which we currently use:
> >
> > select T.Column1,
> > T.Column2,h
> > T.Column3,
> > T2.Column2,
> > (Select S.SpecialColumn1 from SoredProc(T.Column1) S),
> > (Select S.SpecialColumn2 from SoredProc(T.Column1) S),
> > (Select S.SpecialColumn3 from SoredProc(T.Column1) S),
> > (Select S.SpecialColumn4 from SoredProc(T.Column1) S)
> > from Table1 T
> > inner join Table2 T2 ON (T2.Column1 = T.Column5)
> > where (T.Column4 <> 5)
> >
> > Now we want to add 1 or two more calls to the procedure to get more
> > information from it we don't want to call the procedure more times if
> > we don't need to.
> >
> > Is there anything we can do so we can call the procedure just once and
> > get all the columns we want from it in one go?
> >
> > We have tried joining onto the stored procedure, e.g.:
> >
> > inner join StoredProc(T.Column1) S on (S.SpecialColumn1 =

T.Column1)

> >
> > but this does not work (error 'The cursor identified in the update or
> > delete statement is not positioned on a row.
> > no current record for fetch operation', so perhaps our stored
> > procedure is getting a null??)
> >
> > I hope you can get the idea of what I am trying.
> >
> > Any help appreciated
> > Regards
> > Colin
> >
>
> Colin,
>
> Use a *left* join to force the table to be the first scanned.
>
> select
> T.Column1,
> T.Column2,
> T.Column3,
> T2.Column2,
> S.SpecialColumn1,
> S.SpecialColumn2,
> S.SpecialColumn3,
> S.SpecialColumn4
> from
> Table1 T
> inner join Table2 T2 ON (T2.Column1 = T.Column5)
> LEFT join SoredProc(T.Column1) S
> where
> (T.Column4 <> 5)
>

You may also need to add a condition to the where clause to ensure
that the left outer join gives an equivalent dataset to the desired
inner join. The inner join (if it worked) would have excluded records
from T and T2 for which no record was returned from S, whereas the
left join would return those records and null.

The easiest way to do this is to add to the query

AND S.SomeField is not null

(Where S.SomeField is never left null by the stored procedure if it
returns ever calls suspend. You could even add a new returning
parameter for this purpose)

On a side note, are there any plans to enhance the optimiser to
recognise that joining stored procedures must happen after the input
values are acquired rather than failing as it does now?

Adam