“Don’t count the days. Make the days count.”
-Muhammad Ali
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
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.
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.
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. After we execute the macro we get two result sets.
CREATE Macro Join_Mac AS
(SELECT E.*, Budget
FROM Employee_Table as E
INNER JOIN
Department_Table as D ON E.Dept_No = D.Dept_No ; ) ;
EXEC Join_Mac ;
Users can create complex joins in macros and then SHARE them with other Users.
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. .
Why did this macro execution error? This Macro expected an Input Parameter and errors unless it gets it.
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. The 2000000 represents the IN_Employee_No and the 44444.44 represents the Salary input parameter.
The execute example above uses named parameters to pass the macro the input parameters. Since the parameter names match the input parameter names in the macro it doesn’t matter what order you place the parameter when executing.
These data attributes are never valid with macro parameters:
•Check constraints
•FROM statements can't be parameterized
•COMPRESS phrase
You cannot pass the names of database objects in a macro as a parameter. Database objects refer to:
•Databases
•Tables
•Views
•View columns
The execute example above uses named parameters to pass the macro the input parameters. Since the parameter names match the input parameter names in the macro it doesn’t matter what order you place the parameter when executing.
CREATE Macro AnyTable_mac
(IN_TableName Char(32))
AS
(SELECT *
FROM :IN_TableName ; ) ;
You can’t pass a parameter to be used in a FROM clause
Error
This Macro failed because you can’t pass an input parameter to a FROM CLAUSE.
ERROR [25000] [Teradata][ODBC
Teradata Driver][Teradata Database]
Data definition not valid unless solitary.
The second macro create fails. Why? Because you can only have one Create statement in a view and it must be the last statement. That is a rule.
CREATE Global Temporary TABLE Dept_Agg_GLO
(Dept_no Integer
,Sum_Salary Decimal(10,2)
)
ON COMMIT PRESERVE ROWS ;
Create Macro clever_Mac
AS
(INSERT INTO Dept_Agg_GLO
SELECT Dept_no
,SUM(Salary)
FROM Employee_Table
GROUP BY Dept_no ;
SELECT *
FROM Dept_Agg_Glo;);
Above we created a Global Temporary table. In our creation of the macro we do an Insert/Select into the Global Temporary table and then we query it inside the macro. This is how you get around the rule that you can only create one table in a macro and it has to be the last table. A Global Temporary table exists permanently once it is created, and it is materialized by an Insert/Select. This is allowed in a macro creation.