|
|
|
Junior Member
 
Group: Forum Members
Last Login: Yesterday @ 11:44:10 AM
Posts: 11,
Visits: 66
|
|
If you create a pure SELECT macro (no INSERT), is there any way of using the macro and its returned set as part of a subquery? Meaning, is there a way to call a macro within another select or insert statement?
Example:
INSERT INTO TABLE_NAME
SELECT * FROM
(
SELECT * FROM OTHER_TABLE WHERE FIELD_NAME = 'A'
UNION
SELECT * FROM OTHER_TABLE WHERE FIELD_NAME = 'B'
UNION
SELECT * FROM OTHER_TABLE WHERE FIELD_NAME = 'C'
);
Is there a way to replace those inner selects with a macro, which would be the equivalent of this non-working query:
INSERT INTO TABLE_NAME
SELECT * FROM
(
EXEC MACRO_NAME('A')
UNION
EXEC MACRO_NAME('B')
UNION
EXEC MACRO_NAME('C')
);
Obviously with this example using the macro would be pointless. But if each of those select statements were hundreds of lines long, with much more complex WHERE clauses, writing out each select statement would be lengthy, and more difficult to understand.
So again, is there a way to use the returned result set of a macro in any other way than standing the macro alone?
Any help is appreciated.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: Yesterday @ 11:44:10 AM
Posts: 11,
Visits: 66
|
|
I guess really all I'm looking for is a way to write a SELECT macro with a bare minimum of WHERE clauses, so I can reuse it multiple times, imposing different WHERE clauses each time.
Say I had this macro:
CREATE MACRO MAC_TEST AS (
SELECT * FROM MY_TABLE WHERE FIELD1 = 'A';
);
Now say I wanted rows from MY_TABLE where FIELD1 = 'A' and FIELD2 = 17, and then later I wanted FIELD1 = 'A' and FIELD3 = 1.7. It would be nice if I could impose the additional constraints while still using the macro, like this:
SELECT * FROM (EXEC MAC_TEST)
WHERE FIELD2 = 17
or
SELECT * FROM (EXEC MAC_TEST)
WHERE FIELD3 = 1.7
I know you can't do that, but is there any way in Teradata to do something similar? All I can think of is to have the macro insert into a temp table, and select from the temp table...but it seems like that would be extremely slow, having a macro select data, insert into a temp table, selecting from the temp table, then inserting into the live table....hundreds of times.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 6:49:01 AM
Posts: 180,
Visits: 578
|
|
Hello,
Macro cannot be used like that, for the task you are trying to achieve, creating view will be the best option.
HTH.
Regards,
Adeel
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: Yesterday @ 11:44:10 AM
Posts: 11,
Visits: 66
|
|
Thanks. I ended up designing my macros with conditional WHERE clauses, depending on what parameter was passed.
Example:
CREATE MACRO My_Macro (FIELD_OPTION VARCHAR(10))
AS (
SELECT * FROM MY_TABLE
WHERE FIELD1 = 'a'
AND ((:FIELD_OPTION = 'field2' AND FIELD2 = 'b')
OR (:FIELD_OPTION = 'field3' and FIELD3 = 'c'));
);
Calling:
EXEC My_Macro('field2');
EXEC My_Macro('field3');
|
|
|
|