Semantic Errors Are Sometimes Not Reported At the Time a Macro Is Created - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

The system checks the macro text for syntax errors, but not for semantic errors for EXEC and DDL statements. Because of this, it is possible to create a macro that is syntactically valid, but not valid semantically. No message is returned when this occurs.

If there are semantic errors in the macro definition, then a message is returned to the requestor when you perform it.

For example, it is possible to create or replace a macro definition that contains an EXEC request for a macro that is not defined in the system. The following example creates the macro without returning an error even though the macro it performs, no_such_macro, is not defined in the database:

     CREATE MACRO test_macro AS (
       EXEC no_such_macro;);

The system creates the macro as requested. It is only when you attempt to perform test_macro that you discover the semantic error:

     EXEC test_macro;

     EXEC test_macro;

     *** Failure 3824 Macro 'no_such_macro' does not exist.
                    Statement# 1, Info =0
       *** Total elapsed time was 1 second.

This failure to enforce semantic correctness is restricted to EXEC requests and DDL requests. If a macro definition contains any DML requests, then the objects those statements reference must exist or the system aborts the attempt to create the macro.

Because the following CREATE MACRO request contains a reference to the table named table_1 that does not exist, the system aborts it and returns an error to the requestor because table_1 does not exist.

     CREATE MACRO test_macro_2 AS (
      SELECT *
      FROM table_1;);

     CREATE MACRO test_macro_2 AS (SELECT * FROM table_1;);

     *** Failure 3807 Object 't1' does not exist.
                    Statement# 1, Info =0
     *** Total elapsed time was 1 second.