THEN/ELSE Expressions Having Mixed Data Types

Teradata Vantage™ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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.
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.
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.

all are implicitly converted to FLOAT and the CASE expression returns a FLOAT result.

Some inaccuracy is inherent and unavoidable when FLOAT data types are involved. Implicit conversion of DECIMAL and INTEGER values to FLOAT values may result in a loss of precision or produce a number that cannot be represented exactly.
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.
An error is generated if the server character set is GRAPHIC.