Archives of the TeradataForum

Message Posted: Fri, 13 Apr 2007 @ 11:34:40 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Macro's don't really work like that. For example a macro could generate result sets of wildly varying structures that won't easily go into a table.

example:

     replace macro x as
     (
     select int1, int2
     from some_table;

     select varchar100, varbyte10, int3, etc
     from some_other_table;
     );

I know yours probably doesn't do this, but hopefully you will see the dilema.

As someone else suggested, an alternative is to modify the select statement (which you can extract if you show the macro) into an insert select which can be placed into the macro or run seperately.

Something that I have seen someone done once is like this:

     replace macro x as
        delete from tgt all;     /* empty the target table */

        insert into tgt          /* Load the target table for future
     reference */
        select blah, blah, blah
        from some_table ...
        ;
        select * from tgt;       /* Return the result set to the user */
     );

	<Prev	Next>		<<First	<Prev	Next>	Last>>