17.10 - View Definitions and Character Sets - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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

Expansion is done according to the following rules:

  • If the clause is applied to an expression within the body of the view, it takes the server character set of the expression if the expression is of type CHARACTER.
  • Otherwise, the server character set is set to the default character set of the creator/modifier of the view.

    The default session character set of the creating user of the view is used because otherwise the user default session character set would depend on each individual user, and two users with different default session character sets could get different results from the view.

    For example, if the CREATE USER default character set is Kanji1, then the view is expanded as shown below.

    Original view definition:

        CREATE TABLE table_1 (
          cu CHARACTER(5) CHARACTER SET  UNICODE);
           
        CREATE VIEW view_1 AS
         SELECT cu (CHARACTER(6))
         FROM table_1;

    Expanded view definition:

        CREATE VIEW v1 AS
         SELECT cu (CHARACTER(6), CHARACTER SET  UNICODE)
         FROM table_1;

    Similarly, the following view definition is expanded as shown below.

    Original view definition:

        CREATE VIEW v2 AS
         SELECT 123 (CHARACTER(12));

    Expanded view definition:

        CREATE VIEW v2 AS
         SELECT 123 (CHARACTER(12), CHARACTER SET KANJI1);