Rules for result data types
Rules that are applied to the operands of an operation determine the data type of the result. Certain rules apply in certain situations and apply depending on the data type of operands.
The rules apply to:
- Corresponding columns in set operations (UNION, INTERSECT, or EXCEPT)
- Result expressions of a CASE expression
Arguments of the scalar functions COALESCE,
IFNULL, MAX, and MIN
- Expression values of the IN list of an IN predicate
- Arguments of a BETWEEN predicate, except if
the data types of all operands are numeric
- Arguments for the aggregation group ranges
in OLAP specifications
For the result data type of expressions that involve the operators '/', '*', '+' and '-', see Expressions with arithmetic operators.
For the result data type of expressions that involve the CONCAT operator, see Expressions with the concatenation operator.
Evaluation of the operands of an operation determines the data type of the result. If an operation has more than one pair of operands, DB2® determines the result type of the first pair, uses this result type with the next operand to determine the next result type, and so on. The last intermediate result type and the last operand determine the result type of the operation.
With the exception of the COALESCE function, the result of an operation can be null unless the operands do not allow nulls.
If the data type and attributes of any operand column are
not the same as those of the result, the operand column values are
converted to conform to the data type and attributes of the result.
The conversion operation is exactly the same as if the values were
assigned to the result. For example:
- If one operand column is CHAR(10), and the other operand column is CHAR(5), the result is CHAR(10), and the values derived from the CHAR(5) column are padded on the right with five blanks.
- If the whole part of a number cannot be preserved then an error is returned.