Hi Set,

Thank you for posting. The solution you told me works perfectly. Both approaches are applicable for what I'm needing.

Hernando.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> >Hi,
> >
> >I'm having trouble with this simple stored procedure:
> >
> >SET TERM !! ;
> >CREATE PROCEDURE test_proc (d_date TIMESTAMP)
> > RETURNS (Result CHAR(50))
> >AS BEGIN
> > Result = CAST(d_date AS CHAR(50));
> > SUSPEND;
> >END !!
> >SET TERM ; !!
> >
> >If I execute this statement it works fine:
> >
> >"SELECT a.my_id, a.some_date, a.a_name, c.my_result FROM some_table a, test_proc(CAST
> >('01/01/2011' AS TIMESTAMP)) c WHERE a.my_id < 10;"
> >
> >But if I execute this other one it raises an exception.
> >
> >"SELECT a.my_id, a.some_date, a.a_name, c.my_result FROM some_table a, test_proc(a.some_date) c >WHERE a.my_id < 10;"
> >
> > ISC ERROR CODE:335544348
> > no current record for fetch operation.
> >
> >What am I doing wrong?
>
> Hi Hernando!
>
> Don't know what's wrong, but I do know that Firebird 1.5 in some cases tried to put the stored procedure ahead of the table in some PLANs. This, of course, does not work when a field of the table is used as an input parameter. The general solution was to use a LEFT JOIN rather than a JOIN (your query use SQL-89, you should really change to SQL-92 which uses explicit rather than implicit JOIN):
>
> SELECT a.my_id, a.some_date, a.a_name, c.my_result
> FROM some_table a
> LEFT JOIN test_proc(a.some_date) c on (1=1)
> WHERE a.my_id < 10;
>
> Another way to achieve the same thing is to use a subselect:
>
> SELECT a.my_id, a.some_date, a.a_name,
> (SELECT c.my_result FROM test_proc(a.some_date)) as my_result
> FROM some_table a
> WHERE a.my_id < 10;
>
> HTH,
> Set
>