On Fri, 18 Feb 2005 17:01:52 -0500, Ann W. Harrison wrote:

>
>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".

Hmmm.... It seems more like a trick...
If it's declared on a procedure, it's instantiated after begin
(empty).
But mantain the other syntax, it's useful when you want
to borrow the metadata from a existing query - which is
not the case here.
See, I can live with it, but it's a strange thing to do...

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

Ops!!!! ;-)

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

Good idea! There's a need to declare the transient, because
of blr check and so, right? This is ok to me.

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

That's exactly the way I work my exports in MSSQL. Hmmm, this will be
good!
I liked it. Behave like a table but works like a table variable. Could
we throw
"EXECUTE STATEMENT" against it? If it could, it'll be music to my
ears!!
It'll be a slap on crappy MSSQL. When the solution for cross-db queries
on FB comes - we could throw MSSQL out of the window!!!
Two years ago I had 3 features missing on FB to a real fast migration
from MSSQL. When your's transient dts comes bundled, there's only
one missing!!