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

>
> 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)

Not in this case. Note that the procedure receives a field as it's
parameter, so the result will be equal to the input parameter, and for
each row from the 'test' table, it returns a diferent result.

Regards,

Fabiano.