Chapter 15 – Macro Functions

“Don’t count the days. Make the days count.”

-Muhammad Ali

The 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

The 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

image

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

image

EXEC Emp2_mac ;

image

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.

Complex Joins inside a Macro

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 ;

image

Users can create complex joins in macros and then SHARE them with other Users.

Passing an INPUT Parameter to a Macro

image

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

image

Why did this macro execution error? This Macro expected an Input Parameter and errors unless it gets it.

An UPDATE Macro with Two Input Parameters

image

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.

Executing a Macro with Named (Not Positional) Parameters

image

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.

Macro Parameter Restrictions

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.

Troubleshooting a Macro

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.

More Troubleshooting of a Macro

image

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.

Clever Tricks

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset