The REPLACE MACRO statement is used to modify an existing macro. It is written virtually the same as the CREATE MACRO because it replaces an existing macro with a new macro, in its entirety. Therefore, the name must be exactly the same, or it will build a new macro. If the wrong name is used and there is another object by the same name, it might replace the wrong macro. It is a very good idea to do a HELP DATADASE before replacing a macro.

The following is the correct syntax format for a REPLACE MACRO:

The next REPLACE MACRO changes Mybetter_macro that was previously built. This change adds a second parameter as a character type and changes the SQL to use the new variable. The new variable becomes a secondary comparison to further define the row(s) for the UPDATE and SELECT. Additionally, it changes the INNER JOIN to a RIGHT OUTER JOIN.

Now that we have a couple of macros, it is time to execute them.


To run a macro, the EXECUTE or EXEC command is used. If the macro expects parameters, they must be included in the EXEC command enclosed in parentheses. One or more missing parameter values cause the execution to fail. The parameters can be provided in a positional sequence or via the parameter name. If the positional sequence is used, it is your responsibility to list them in the same sequence that they appear in the CREATE MACRO statement. Otherwise use the name of the parameter to set the value.

The syntax for executing a macro is:

EXEC <macro-name> [ ( <parameter-value-list> ) ] ;

The following are examples to execute both of the macros created above:

The EXEC for Myfirst_macro is:
EXEC Myfirst_macro;

Since there are no parameters, no values are provided in the EXEC command. The next three EXEC commands are all valid for executing Mybetter_macro that contains two parameters:

The first EXEC above uses positional assignment of values to the parameters in the macro. The order of the values in the EXEC is very important. The next three EXEC commands use the parameter name to assign values. Notice that when the parameter names are used, their sequence in the EXEC does not matter. The EXEC simply matches the names and assigns the values to each variable. Also notice that when all parameter values are present, it is shorter to use a positional assignment list for the values.

All SQL contained in a macro is treated as a single transaction and any output is returned to the user as if it were run directly. The output report from Mybetter_macro using the above values at execution looks like this:

1 Row Returned

Row Returned

If the second value of the macro parameter needed to be a NULL, any of the EXEC commands below accomplish this:

Since the second value is null, the REPLACE MACRO added the IS NULL comparison. As a result, the above execution returns the following row:

1 Row Returned

Row Returned

Since macros contain SQL, you can EXPLAIN a macro:

EXPLAIN EXEC Myfirst_macro; EXPLAIN EXEC Mybetter_macro (1028, NULL);

Although the above EXPLAIN works with actual data values passed to it, the explanation is not an accurate estimation. This is due to the nature of the presence of the literal values opposed to dynamic values being passed to a macro in a production environment.

The following displays a more accurate estimation when using parameterized macros:

USING A INT, B CHAR(3)EXPLAIN EXEC Mybetter_macro (:A, :B);

Note The USING is called a Modifier because it modifies the SQL statement that follows it. Its function is to establish variable names with data types. Then, these names are available for use in the SQL statement. The EXPLAIN is another modifier.

As a matter of discussion, the parameter values should match the data type of the columns referenced in the SQL. If they do not match, a conversion must be performed. Plus, we have seen situations where the type difference caused the optimizer to not use a PI and did a full table scan instead. Be sure to EXPLAIN macros before putting them into production.

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

Teradata Topics