Hello,
I need to know what's wrong in this translation from Informix. Note the
difference in the results, which is due to the UPDATE statement.
ORIGINAL PROGRAM (INFORMIX):
---------------------------
create table a(a int)
;
insert into a values(1)
;
insert into a values(1)
;
insert into a values(2)
;
create temp table c(a smallint)
;
create procedure pepe()
define b smallint;
foreach select a into b from a where a in(1,2)
update a set a = 0 where a = 1;
insert into c values(b);
end foreach;
end procedure
;
execute procedure pepe()
;
select * from c
;
drop procedure pepe
;
drop table a
;
drop table c
;
RESULTS:
-------
1
1
2
TRANSLATION TO DB2:
------------------
create table a (a integer)
@
insert into a values (1)
@
insert into a values (1)
@
insert into a values (2)
@
declare global temporary table session.c (a smallint) on commit
preserve rows
@
create procedure pepe()
begin
declare b smallint;
begin
declare f_foreach_pepe_1 integer default 0;
declare c_foreach_pepe_1 cursor for select a from a where a in (1,
2);
declare continue handler for not found set f_foreach_pepe_1 = 1;
open c_foreach_pepe_1;
fetch_loop_pepe_1:
loop
fetch c_foreach_pepe_1 into b;
if f_foreach_pepe_1 = 1 then leave fetch_loop_pepe_1; end if;
begin
update a set a = 0 where a = 1;
insert into session.c values (b);
end;
end loop;
close c_foreach_pepe_1;
end;
end
@
call pepe()
@
select * from session.c
@
drop procedure pepe
@
drop table a
@
drop table session.c
@
RESULTS:
-------
1
1
