15.00 - Rules for the CASE Expression Result Type - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

Rules for the CASE Expression Result Type

Because the expressions in CASE THEN/ELSE clauses can be different data types, determining the result type is not always straightforward. You can use the TYPE attribute function with the CASE expression as the argument to find out the result data type. See “TYPE” on page 301.

The following rules apply to the data type of the CASE expression result.

THEN/ELSE Expressions Having the Same Non-Character Data Type

If all of the THEN and ELSE expressions have the same non-character data type, the result of the CASE expression is that type. For example, if all of the THEN and ELSE expressions have an INTEGER type, the result type of the CASE expression is INTEGER.

For information about how the precision and scale of DECIMAL results are calculated, see “Binary Arithmetic Result Data Types” on page 110.

THEN/ELSE Character Type Expressions

The following rules apply to CASE expressions where the data types of all of the THEN/ELSE expressions are character:

  • The result of the CASE expression is also a character data type, with the length equal to the maximum length of the different character data types of the THEN/ELSE expressions.
  • If the data types of all of the THEN/ELSE expressions are CHARACTER (or CHAR), the result data type is CHARACTER. If one or more expressions are VARCHAR (or LONG VARCHAR), the result data type is VARCHAR.
  • The server character set of the result is determined as follows:
  • If the CASE expression contains 1 nonliteral character expression and 1 or more literals, then Teradata Database tries to translate every literal to the character set of the nonliteral. If the translations are successful, then the character set of the nonliteral is used for the result data type. If the translations are not successful, the server character set of the result is Unicode.
  • If the CASE expression contains more than 1 nonliteral character expression and 1 or more literals, then:
  • i If all of the nonliteral expressions have the same character set, then Teradata Database uses this character set as the common data type. Otherwise, if the nonliteral expressions have differing character sets, then Teradata Database uses the Unicode character set as the common data type.

    i If all of the nonliteral expressions have the same character set, then Teradata Database uses this character set as the common data type. Otherwise, if the nonliteral expressions have differing character sets, then Teradata Database uses the Unicode character set as the common data type.

    ii Teradata Database tries to translate every literal to the character set of the common data type. If the translations are successful, then the result data type has the character set of the common data type. If the translations are not successful, the server character set of the result is Unicode.

    Examples of Character Data in a CASE Expression

    For the following examples of CHARACTER data behavior, assume the default server character set is KANJI1 and the table definition for the CASE examples is as follow:

       CREATE TABLE 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
       );

    Caution:

    In accordance with Teradata internationalization plans, KANJI1 support is deprecated and is to be discontinued in the near future. KANJI1 is not allowed as a default character set; the system changes the KANJI1 default character set to the UNICODE character set. Creation of new KANJI1 objects is highly restricted. Although many KANJI1 queries and applications may continue to operate, sites using KANJI1 should convert to another character set as soon as possible. “”

    Example  

    The server character set of the result of the following query is UNICODE because the CASE expression contains more than 1 nonliteral character expressions with differing character sets.

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

    Example  

    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;

    Example  

    One THEN/ELSE expression in the following query has a Unicode column. The query is successful and the result data type is UNICODE because the CASE expression contains 1 Unicode column and all other literals can be successfully translated to Unicode.

       SELECT i, CASE
                   WHEN i=1 THEN column_u
                   WHEN i=2 THEN 'abc'
                   WHEN i=3 THEN 8
                   WHEN i=4 THEN _KanjiSJIS'4142'XC
                   ELSE 10
                 END
       FROM table_1
       ORDER BY 1;

    Example  

    One THEN/ELSE expression in the following query has a Latin column. The query is successful and the result data type is Latin because the other literals can be successfully translated to Latin.

       SELECT i, CASE
                   WHEN i=1 THEN 'abc'
                   WHEN i=2 THEN column_l
                   ELSE 'def'
                 END
       FROM table_1
       ORDER BY 1;

    THEN/ELSE Expressions Having Mixed Data Types

    The rules for mixed data appear in the following table:

     

    IF the THEN / ELSE clause expressions …

    THEN …

    consist of BYTE and/or VARBYTE data types

    if the data types of all of the THEN/ELSE expressions are BYTE, the result data type is BYTE. If one or more expressions are VARBYTE, the result data type is VARBYTE.

    The result has a length equal to the maximum length of the different byte data types.

    contain a DateTime or Interval data type

    all of the THEN/ELSE clause expressions must have the same data type.

    contain a FLOAT (approximate numeric) and no character strings

    the CASE expression returns a FLOAT result.

    Note: Some inaccuracy is inherent and unavoidable when FLOAT data types are involved.

    are composed only of DECIMAL data

    the CASE expression returns a DECIMAL result.

    Note: A DECIMAL arithmetic result can have up to 38 digits. A result larger than 38 digits produces a numeric overflow error.

    For information about how the precision and scale of DECIMAL results are calculated, see “Binary Arithmetic Result Data Types” on page 110.

    are composed only of mixed DECIMAL, BYTEINT, SMALLINT, INTEGER, and BIGINT data

    are a mix of BYTEINT, SMALLINT, INTEGER, and BIGINT data

    the resulting type is the largest type of any of the THEN/ELSE clause expressions, where the following list orders the types from largest to smallest:

  • BIGINT
  • INTEGER
  • SMALLINT
  • BYTEINT
  • are composed only of numeric and character data

    the numeric data is converted to CHARACTER with a length as determined by the format associated with the numeric expression. Then, the rules for the result data type for character, length, and character set are applied. For details, see “THEN/ELSE Character Type Expressions” on page 571.

    Note: An error is generated if the server character set is GRAPHIC.

    Examples of Numeric Data in a CASE Expression

    For the following examples of numeric data behavior, assume the following table definitions for the CASE examples:

       CREATE TABLE dec22
          (column_l INTEGER
          ,column_2 INTEGER
          ,column_3 DECIMAL(22,2) );

    Example 1

    In the following statement, the CASE expression fails when column_2 contains the value 1 and column_3 contains the value 11223344556677889900.12 because the result is a DECIMAL value that requires more than 38 digits of precision:

       SELECT SUM (CASE
                    WHEN column_2=1 
                    THEN column_3 * 6.112233445566778800000
                    ELSE column_3
                   END )
       FROM dec22;

    Example 2

    The following query corrects the problem in Example 1 by shortening the scale of the multiplier in the THEN expression:

       SELECT SUM (CASE
                    WHEN column_2=1 
                    THEN column_3 * 6.1122334455667788
                    ELSE column_3
                   END )
       FROM dec22;

    Example 3

    In the following query, the CASE expression returns a DECIMAL(38,2) result because the THEN  and  ELSE clauses contain DECIMAL values:

       SELECT SUM (CASE
                    WHEN column_2=1 
                    THEN column_3 * 6
                    ELSE column_3
                   END )
       FROM dec22;

    Examples of Character and Numeric Data in a CASE Expression

    The following examples illustrate the behavior of queries containing CASE expressions with a THEN/ELSE clause composed of numeric and character data.

    Example  

    In the following query, the CASE expression returns a VARCHAR result because the THEN  and  ELSE clause contains FLOAT and VARCHAR values. The length of the result is 30 since the default format for FLOAT is a string less than 30 characters, and USER is defined as VARCHAR(30) CHARACTER SET UNICODE. The result is CHARACTER SET UNICODE because USER is UNICODE.

       SELECT a, CASE
                    WHEN a=1 
                    THEN TIME
                    ELSE USER
                   END
       FROM table_1
       ORDER BY 1;

    Example  

    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;