>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
