Chapter 28
Macro Functions
“Don't count the days. Make the days count.”
Table of Contents Chapter 28 – Macro Functions
– CREATING and EXECUTING a Simple Macro
– Multiple SQL Statements inside a Macro
– Complex Joins inside a Macro
– Passing an INPUT Parameter to a Macro
– Troubleshooting a Macro with INPUT Parameters
– Troubleshooting a Macro with INPUT Parameters
– An UPDATE Macro with Two Input Parameters
– Executing a Macro with Named (Not Positional) Parameters
The 14 rules of Macros
1. Macros can contain one or more SQL Statements
2. Macros are a database Extension in Teradata and not ANSI-Standard
3. Macros don't require PERM Space
4. Macros are stored in the Data Dictionary in the USER DBC
5. Every statement in the macro must end in a semi-colon ;
6. Only one DDL Statement can exist in a Macro and it must be the last statement
7. Input Parameters can be passed to the Macro
8. Macros run when users use the EXEC Macro Command
9. Macros can be executed from any viable front end, especially the Nexus
10. Macros can call views and even other Macros
11. All SQL Statements inside a Macro are considered one Transaction
12. Each Macro name must be a unique object within the database it resides
13. USERS need only the EXEC privilege to run a macro
14. No underlying privileges to tables inside the macro need be required
Macros are database extensions and reside in the Data Dictionary in DBC.
CREATING and EXECUTING a Simple Macro
Only ONE transaction will run by executing any macro. Every Macro will have a semi-colon to end each SQL statement and an additional semi-colon to end the CREATE.
Multiple SQL Statements inside a Macro
CREATE Macro Emp2_mac AS
(SELECT * FROM Employee_Table
WHERE Dept_No = 400 ;
SELECT * FROM Department_Table
WHERE Dept_No = 400 ; ) ;
EXEC Emp2_mac ;
Still, only ONE transaction ran by executing the macro here. Everything in a macro is considered ONE transaction. Notice we have two SQL statements that end with a semi-colon, but we also have a semi-colon to end the CREATE MACRO Statements.
Complex Joins inside a Macro
CREATE Macro Emp3_mac AS
(SELECT E.*, Mgr_No, Budget
FROM Employee_Table as E
INNER JOIN
Department_Table as D ON E.Dept_No = D.Dept_No ; ) ;
Users can create complex joins in macros and then SHARE them with other Users.
Passing an INPUT Parameter to a Macro
The IN_Dept_No represent INPUT Parameters in the above Macro. We must place something within the parenthesis or the macro will not work, because it is expecting the USER to tell it the value of IN_Dept_No in the EXEC statement..
Troubleshooting a Macro with INPUT Parameters
This Macro expected an Input Parameter and errors unless it gets it.
Troubleshooting a Macro with INPUT Parameters
This Macro expected an Input Parameter and errors unless it gets it.
An UPDATE Macro with Two Input Parameters
This Macro expected two parameters so we placed them in the proper order (Position). Since the CREATE statement listed both and IN_Employee_No was listed first it will be in the first position for parameters in the EXEC statement inside brackets.
Executing a Macro with Named (Not Positional) Parameters
Both Exec statements are the same except how they pass the Macro Input Parameters.
Troubleshooting a Macro
This Macro failed because you can't pass an input parameter to a FROM CLAUSE.