15.00 - Macro Definitions and Server Character Sets - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Macro Definitions and Server Character Sets

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. Otherwise, Teradata Database aborts the request and returns an error to the requestor.