Macros in Teradata

[Queryman]

I have macros that have DDL statements in them. Since there is a restriction of one DDL statement per macro, is there a way to have one macro which has a DDL statement call another one which also has a DDL statement. I tried the following but that is not working when I try and run the macro with the following command
exec ba_proddb_v.derek_chain_list
I get the following error
3932: Only a ET or null statement is legal after a DDL statement
I am using Queryman, not BTEQ.

I created the macro as follows:
replace macro BA_PRODDB_V.DEREK_CHAIN_LIST
AS (
exec ba_proddb_v.derek_chain_list1;
exec ba_proddb_v.derek_chain_list2;
);


Thanks!

QueryMan

 

[Patten]

Why don't you use a stored procedure? It does not have the limitation of one DDL statement per stored procedure.

Because your previous statement won't work still because of that DDL limitation: you are still doing one DDL statement per called macro derek_chain_listx I suppose.

 

[Queryman]

How would I code the above & invoke it using a SP. I haven't used it so far?
Thanks!

QueryMan

 

[Patten]

create procedure BA_PRODDB_V.DEREK_CHAIN_LIST
begin

exec ba_proddb_v.derek_chain_list1;
exec ba_proddb_v.derek_chain_list2;

end;



You could also copy paste directly the SQL code of your two macros between the begin and end statement. In this way you don't need anymore the two macros.

 

[Queryman]

Thanks, I think the syntax must be different, I tried your suggestion and got the following error

SPL1075:E(L2), The left paranthesis in parameter declaration is missing.


SPL1076:E(L2), The right paranthesis in parameter declaration is missing.


SPL1018:E(L3), Unexpected text 'exec' in place of keyword BEGIN.


SPL1043:E(L3), Unexpected text 'exec' in place of keyword END.


SPL1059:E(L3), Unexpected text 'ba_proddb_v' in place of semicolon.


SPL1059:E(L3), Unexpected text '.' in place of semicolon.


SPL1033:E(L3), Unexpected text 'derek_chain_list1'.


SPL1033:E(L3), Unexpected text ';'.




QueryMan

 

[Patten]

Excuses, excuses: my fault: I forgot the parenthesises.
This should help you out:


create procedure BA_PRODDB_V.DEREK_CHAIN_LIST()
begin

exec ba_proddb_v.derek_chain_list1;
exec ba_proddb_v.derek_chain_list2;

end;