fabiano_bonin wrote:

>Hi all!
>
>I have just noted a behavior when joining stored procedures, that
>seems strange to me, but will be very usefull for me in some cases.
>
>I'm reproducing it here to know if it's as designed and if it will be
>manteined in the next versions, because i'm planning to use it a lot.
>
>I will create a stored procedure that receives a parameter and returns
>2 rows, both containing the same value passed to the parameter. I will
>also create a table and insert 3 rows in it.
>
>set term !! ;
>
>create or alter procedure sp_test (
> p_test integer )
>returns (
> test integer )
>as
>begin
> test = p_test;
> suspend;
> suspend;
> exit;
>end !!
>
>set term ; !!
>
>create table test (
> test_id integer not null primary key,
> field1 integer );
>
>insert into test (test_id, field1) values (1, 1);
>insert into test (test_id, field1) values (2, 2);
>insert into test (test_id, field1) values (3, 3);
>commit;
>
>-- If i try to select INNER JOINING the procedure,
>-- i have an error.
>
>select
> a.*
>from
> test a
> inner join sp_test(a.test_id) b on 1 = 1
>
>-- Statement failed, SQLCODE = -508
>-- no current record for fetch operation
>
>-- If i try to select LEFT JOINING the procedure,
>-- it works and it seems the procedure is evaluated one time for each
> row of the 'test' table.
>
>select
> *
>from
> test a
> left join sp_test(a.test_id) b on 1 = 1
>
>/*
> TEST_ID FIELD1 TEST
>============ ============ ============
>
> 1 1 1
> 1 1 1
> 2 2 2
> 2 2 2
> 3 3 3
> 3 3 3
>*/
>
>Regards,
>
>Fabiano.
>
>

Fabiano,

Join with SP's must use left joins (in the currente state of firebird)
to force it to be the last joinned stream.

Don't know if it is evaluated for every row.. I think the engine
materialize it first and then apply your join condition, since you join
condition is (1=1) it will be always true, if the SP returns two records
you will see "duplicate" rows and the result set (one for each SP
returned value)

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br