Resolution of Non-Qualified Names - 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™

When a CREATE MACRO request is performed, the system parses the text of the requests specified in the macro and resolves any names that are not fully qualified in those requests. Note that non-qualified names in DDL requests in macros are left unresolved.

Consider the following example, in which the default database for user xyz is database abc, and the database for user lmn is database is def. User xyz defines macro_1 as follows:

     CREATE MACRO macro_1 AS (
       CREATE TABLE mambo (
         column_1 INTEGER,
         column_2 INTEGER);
        );

The fully qualified table name is not specified in the CREATE TABLE request.

Because CREATE TABLE is a DDL statement, the name of its containing database is not resolved when the macro is created. Instead, the name is resolved later, when the macro is performed. The result is that the macro always observes the correct storage hierarchy when it is performed.

The following table shows how this works:

IF this user performs the macro named macro_1 THEN a table named mambo is created in this database …
xyz abc
lmn def

Now suppose that user xyz creates the macro named macro_2 as indicated here:

    CREATE MACRO macro_2 AS (
    UPDATE tango
    SET counter = counter + 1;);

Note that the fully-qualified table name is not specified in the UPDATE request.

Because UPDATE is a DML statement, it is fully resolved when macro_2 is created. Subsequently, performing macro_2 causes abc.tango to be updated, whether performed by abc, lmn, or any other user.

To summarize, the following rules apply to non-qualified names when they are specified in macros:

  • Non-qualified names in a macro definition are not resolved in DDL statements when the macro is created. They are not resolved until the macro is performed.
  • Non-qualified names in a macro definition are fully resolved in DML statements when the macro is created.