CREATE MACRO Teradata

The CREATE MACRO or CM statement initially builds a new macro. It names the macro and optionally the database where it is to be created. Additionally, it must specify the SQL statement(s) that comprise the execution of the macro.

Each SQL statement within a macro must have its own semi-colon to help the optimizer delineate one SQL statement from another. All the SQL statements must be enclosed in parentheses to be created and treated as a single transaction.

The following two syntax formats are both valid for a CREATE MACRO:

Here is the creation of a simple macro:

Notice first that the macro contains two SQL statements: UPDATE and SELECT. Next, both statements are enclosed in the parentheses, unlike a CREATE VIEW and lastly, each of these statements ends with a semi-colon (;) so the optimizer can determine the scope of each command.

When the above macro is executed, it updates the value in column1 by adding 10 to it for the row(s) identified with a value of 1024 in column2. Then, it immediately turns around and selects the row(s) to display the result of the update operation.

As good as this might be, it is still limited to the row(s) with the value of 1024 in column2. To make the macro more flexible and functional, parameter values can be passed to it dynamically at execution time. That way, each time the macro runs it can change the value in column1 for any row. So, the first run can update rows with 1024 in column2 and the next run perform the same processing, but for the row(s) containing a value of 1028 or any other value in column2.

In order to take advantage of the ability to pass a parameter value, the macro must be built to expect a value and then substitute the value into the SQL statement(s). This gives more power, more flexibility and ease of use to the macro. Therefore, parameters are normally part of a macro and a very good technique.

The next CREATE MACRO incorporates a parameter called invalue:

The parameter must be defined within the macro. As seen above, a parameter called invalue is defined as an INTEGER data type within parentheses following the name of the macro. Any valid Teradata data type is acceptable for use within a macro. Once a variable name and data type are defined, the variable name can be substituted within the macro as many times as needed.

Now that the parameter has a name, the optimizer must be able to distinguish the parameter name from the names of tables and columns. To make this distinction, a colon (:) precedes the name of the parameter. The colon notifies the optimizer that invalue is a variable and not a column found in the DD. Instead, it takes the value stored there and substitutes it into one or more SQL statements.

To add more power and flexibility, additional parameters can be added to the macro. However, every parameter defined must be given a value at execution time. If the parameter list is too long or too short, an error occurs and the execution stops. Now, Mybetter_macro expects one parameter to be passed to it at execution time. The command to execute Mybetter_macro is shown below. However, the method to modify a macro is covered first.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics