Gustavo Randich wrote:
Hello,
I'm writing an automatic SQL parser and translator from Informix to
DB2. Now I'm faced with one of the most difficult things to translate,
the "foreach execute procedure" functionality provided by Informix.
This is the problem: the execution of the translated SQL leaves the
rows in the temp table correctly but raises error SQL0480N. It's very
simple to try it:
------------
Original SQL:
------------
create temp table a(a smallint)
;
drop procedure a
;
create procedure a() returning smallint;
return 1 with resume;
return 2 with resume;
end procedure
;
drop procedure b
;
create procedure b()
define b smallint;
foreach execute procedure a() into b
insert into a values(b);
end foreach;
end procedure
;
execute procedure b()
;
select * from a
;
drop table a
;
Not knowing Informix well enough, I'd say that your procedure "A" is just a
table function, i.e. each call made to the function returns the next row.
In DB2 you could achieve something like it in the following way:
CREATE FUNCTION a()
RETURNS TABLE ( col SMALLINT )
LANGUAGE SQL
RETURN SELECT *
FROM TABLE ( VALUES ( 1 ),
( 2 ) ) AS t
@
Now you can use this function in procedure B:
CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
FOR fetch AS loop CURSOR FOR SELECT * FROM TABLE ( a() ) AS t
DO
INSERT INTO a VALUES (col);
END FOR;
END
@
In this particular case, I would even further simplify the procedure:
CREATE PROCEDURE b()
LANGUAGE SQL
BEGIN
INSERT INTO a SELECT col FROM TABLE ( a() ) AS t;
END
@
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
