On 17/09/2010 13:49, Werner F. Bruhin wrote:
> 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.
>
Was missing a begin/end, so it should be like this:

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
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
end;

Sorry for the noise
Werner