16.20 - Macro Definitions and Server Character Sets - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

A macro can contain explicit [VAR]CHARACTER(n) [CHARACTER SET …] clauses. If the server character set (for example, CHARACTER SET LATIN) is not specified, the macro definition expands the [VAR]CHARACTER(n) clause.

Expansion is done according to the following rules:
  • If the clause is applied to the macro parameter, it takes the user default server character set of the creator or modifier of the macro (creator user default server character set).

    For example, if the creator user default server character set is Unicode, then the macro is expanded as shown in the immediately following example:

    Original macro definition:

         CREATE MACRO mm AS (
           column_a CHARACTER(5))
           (SELECT :column_a;);

    Expanded macro definition:

         CREATE MACRO mm AS (
           column_a CHARACTER(5) CHARACTER SET  UNICODE)
          (SELECT :column_a;);
  • If the clause is applied to an expression within the body of the macro, it takes the server character set of the expression if the expression is typed as CHARACTER. Otherwise, the server character set is set to the default server character set for the creator user.

    For example, if the creator user default server character set is UNICODE, then the macro is qualified as shown in the immediately following example when it is performed.

    Original macro definition:

         CREATE TABLE table_2 (
           ck CHARACTER(5) CHARACTER SET KANJI1);
    
         CREATE MACRO mm AS (
           SELECT ck (CHARACTER(6))
           FROM table_2;);

    Qualified macro definition:

         SELECT ck (CHARACTER(6), CHARACTER SET KANJI1)
         FROM table_2;

    When the following macro is performed, it is qualified as seen in the SELECT request that immediately follows it.

    Original macro definition:

         CREATE MACRO mm AS (
           SELECT 123 (CHARACTER(12)););

    Qualified macro definition:

         SELECT 123 (CHARACTER(6), CHARACTER SET  UNICODE);
  • If the macro contains a CREATE TABLE or CREATE VIEW with character definition and the CHARACTER SET clause is omitted, the macro expansion takes the user default server character set of the user who performs it.

    For example, suppose the creator user default server character set is UNICODE. When the macro definition below is performed by a different user having a user default server character set of LATIN, then table_1 is created as shown in the immediately following example.

    Original macro definition:

         CREATE MACRO tt AS (
           CREATE TABLE table_1
           (column_l CHARACTER(5)););

    Expanded macro definition:

         CREATE TABLE table_1 (
           column_l CHARACTER(5) CHARACTER SET LATIN NOT CASESPECIFIC);

    The same applies to a CREATE VIEW statement within a CREATE MACRO statement.

You cannot specify a character data type that has a server character set of KANJI1.