16.20 - Examples of Character Data in a CASE Expression: Example 2 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

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

The result of the following query is a 5354 failure (Arguments must be of type KANJI1) because one THEN/ELSE expression is a KANJI1 literal, but the server character sets of all the other THEN/ELSE expressions are not KANJI1.

   SELECT i, CASE
               WHEN i=1 THEN column_l
               WHEN i=2 THEN column_u
               WHEN i=3 THEN column_j
               WHEN i=4 THEN column_g
               WHEN i=5 THEN _Kanji1'4142'XC
               ELSE column_k
             END
   FROM table_1
   ORDER BY 1;

For this example, assume the following table definition:

   CREATE table_1
     (i        INTEGER,
      column_l CHARACTER(10) CHARACTER SET LATIN,
      column_u CHARACTER(10) CHARACTER SET UNICODE,
      column_j CHARACTER(10) CHARACTER SET KANJISJIS,
      column_g CHARACTER(10) CHARACTER SET GRAPHIC,
      column_k CHARACTER(10) CHARACTER SET KANJI1);

The following query fails because the server character set is GRAPHIC (because the server character set of the first THEN with a character type is GRAPHIC):

   SELECT i, CASE
               WHEN i=1 THEN 4
               WHEN i=2 THEN column_g
               WHEN i=3 THEN 5
               WHEN i=4 THEN column_l
               WHEN i=5 THEN column_k
               ELSE 10
             END
   FROM table_1
   ORDER BY 1;