Chapter 10 - Macro Functions
“Don't count the days. Make the days count.”
The 14 rules of Macros
- Macros can contain one or more SQL Statements
- Macros are a database Extension in Teradata and not ANSI-Standard
- Macros do NOT require PERM Space
- Macros are stored in the Data Dictionary in the USER DBC
- Every statement in the macro must end in a semi-colon ;
- Only one DDL Statement can exist in a Macro and it must be the last statement
- Input Parameters can be passed to the Macro
- Macros run when users use the EXEC Macro Command
- Macros can be executed from any viable front end, especially the Nexus
- Macros can call views and even other Macros
- All SQL Statements inside a Macro are considered one Transaction
- Each Macro name must be a unique object within the database it resides
- USERS need only the EXEC privilege to run a macro
- 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 ; ) ; |
Two Queries
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 Join_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 parentheses 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.