Hello Werner



The suspend is not in the "do" loop of the "for select", so there is
always only one row, the last of the selec, returned.



begin
for
select id, name, iso2, iso3, telcode, created_at, updated_at from
countries
into :id, :name, :iso2, :iso3, :telcode, :created_at, :updated_at
do
begin
select name from countries_l
where :id = countries_l.fk_country_id and
countries_l.lang_code5 = rdb$get_context('USER_SESSION',
'LANG_CODE')
into :trans_name;
end
if (:trans_name is not Null) then
begin
name = :trans_name;
end
suspend;
end



Regards

Chris



My test table "countries" contains two rows and with this stored
procedure I only get one row.

CREATE OR ALTER PROCEDURE COUNTRIES_LP
returns (
id bigint,
name varchar(30),
iso2 varchar(2),
iso3 varchar(3),
telcode smallint,
created_at date,
updated_at timestamp)
as
declare variable trans_name varchar(30);
begin
for
select id, name, iso2, iso3, telcode, created_at, updated_at from
countries
into :id, :name, :iso2, :iso3, :telcode, :created_at, :updated_at
do
begin
select name from countries_l
where :id = countries_l.fk_country_id and
countries_l.lang_code5 = rdb$get_context('USER_SESSION',
'LANG_CODE')
into :trans_name;
end
if (:trans_name is not Null) then
begin
name = :trans_name;
end
suspend;
end;

If I remove the select on "countries_l" I see the two rows. I thought
between the "do" and the "suspend" I can have other selects etc to
change what is being output, but I am obviously missing something.

Can someone please put me on the right path.

Thanks
Werner





[Non-text portions of this message have been removed]