Hi Set,
Thank you for posting. The solution you told me works perfectly. Both approaches are applicable for what I'm needing. Hernando. --- In [email protected], 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 >
