Fabricio Araujo wrote:

>
> Could I create such a proc with the transient datasets?

Yes ... but that's not the way I would do it. To follow the logic of
your example, you'd need to declare variables: name, date_born, and
salary_incr. You'd also need to create a transient data set using a
statement like:

"select 'abc', 1, 1, from rdb$database yielding export_trans".

Then delete everything in it, and use it just as you did above.

> create procedure trans_export(counter integer)
> as

declare name varchar (30);
declare date_born date;
declare salary_incr integer;

> declare export_trans transient data set
> (name varchar (30), age integer, salary integer);
> begin

select 'abc', 1, 1 from rdb$database yielding export_trans;
delete from export_trans;

> for select e.name, e.date_born, e.salary_incr from
> employee e inner join salary_mov s on
> s.Id_Emp = e.Id_emp
> where (<some condition>)
> into :name, :date_born, :salary_incr
> do begin
> <do some transformations and denormalizations>
> insert into export_trans .... values...
> <do some more transformations>
> insert into for_import ..... values ....
> end
> end

I'd be more likely to do some of transformations in the original select.
In this case, I'm assuming that you've got a procedure or UDF that
takes a birth date and computes an age - I've written it as if it were a
stored procedure called "birth_date_to_age". I'm also assuming that you
can get a value from the salary_mov table that can be added to the
salary_incr to produce salary:


create procedure trans_export(counter integer)
as
declare export_trans transient data set
(name varchar (30), age integer, salary integer);
begin
select e.name,
(select age from birth_date_to_age (e.date_born)),
s.salary + e.salary_incr
from employee e
inner join salary_mov s on s.Id_Emp = e.Id_Emp
where (<some condition>)
yielding export_trans;

For the next round of transformations, you've got a couple of
alternatives. One is to transform the export_trans itself
progressively. Suppose you want to consider only the highest paid
people at each age ...

select e.name, e.age, e.salary
from export_trans
where e.salary = (select max (e1.salary)
from export_trans e1
where e1.age = e.age)
yielding export_trans;

When you're done transforming export_trans, just dump it into the output
table:

insert into for_import (<columns>)
select name, age, salary from export_trans;

Another is to explicity iterate through export_trans, change values,
and create your output table, This of course requires a few more
procedure variables:

for select e.name, e.age, e.salary from export_trans
into :name, :age, :salary
do begin
<do some transformations and denormalizations>
<do some more transformations>
insert into for_import ..... values ....
end
end


Regards,


Ann