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.