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 the following elements:

  • 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
  • Expression values for the elements in an array constructor
  • Expression values for the arguments for a collection-derived table (UNNEST specification)
  • 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 Arithmetic operators in expressions.

For the result data type of expressions that involve the CONCAT operator, see Concatenation operators in expressions.

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.

Numeric operands

Numeric types are compatible only with other numeric types.

Table 1. Result data types with numeric operands
One operand Other operand Data type of the result
SMALLINT SMALLINT SMALLINT
INTEGER INTEGER INTEGER
INTEGER SMALLINT INTEGER
BIGINT SMALLINT BIGINT
BIGINT INTEGER BIGINT
BIGINT BIGINT BIGINT
DECIMAL(w,x) SMALLINT
DECIMAL(p,x) where
p = x+max(w-x,5)1
DECIMAL(w,x) INTEGER
DECIMAL(p,x)  where
p = x+max(w-x,11)1
DECIMAL(w,x) BIGINT
DECIMAL(p,x) where
p = x+max(w-x,19)1
DECIMAL(w,x) DECIMAL(y,z)
DECIMAL(p,s) where
p = max(x,z)+max(w-x,y-z)1s = max(x,z)
REAL REAL REAL
REAL DECIMAL, BIGINT, INTEGER, or SMALLINT DOUBLE
REAL BIGINT DOUBLE
DOUBLE DOUBLE, REAL, DECIMAL, BIGINT, INTEGER, or SMALLINT DOUBLE
DECFLOAT(n) SMALLINT DECFLOAT(n)
DECFLOAT(n) INTEGER DECFLOAT(n)
DECFLOAT(n) BIGINT DECFLOAT(34)
DECFLOAT(n) DECIMAL(<=16,s) DECFLOAT(n)
DECFLOAT(n) DECIMAL(>16,s) DECFLOAT (34)
DECFLOAT(n) REAL DECFLOAT(n)
DECFLOAT(n) DOUBLE DECFLOAT(n)
DECFLOAT(n) DECFLOAT(m) DECFLOAT(max(n,m))
Notes:
  1. Precision cannot exceed 31.

Character and graphic string operands

Character and graphic strings are compatible with other character and graphic strings as long as there is a conversion between their corresponding CCSIDs.
Table 2. Result data types with string operands
One operand Other operand Data type of the result
CHAR(x) CHAR(y) CHAR(z) where z = max(x,y)
GRAPHIC(x) CHAR(y) VARGRAPHIC(y) where y > maximum length of a graphic
GRAPHIC(x) CHAR(y) GRAPHIC(z) where z = max(x,y)
VARCHAR(x) VARCHAR(y) or CHAR(y) VARCHAR(z) where z = max(x,y)
VARCHAR(x) GRAPHIC(y) VARGRAPHIC(z) where z = max(x,y)
VARGRAPHIC(x) VARGRAPHIC(y), GRAPHIC(y), VARCHAR(y), or CHAR(y) VARGRAPHIC(z) where z = max(x,y)
CLOB(x) CLOB(y), VARCHAR(y), or CHAR(y) CLOB(z) where z = max(x,y)
CLOB(x) GRAPHIC(y) or VARGRAPHIC(y) DBCLOB(z) where z = max(x,y)
DBCLOB(x) CHAR(y), VARCHAR(y), CLOB(y), GRAPHIC(y), VARGRAPHIC(y), or DBCLOB(y) DBCLOB(z) where z = max(x,y)
Character string subtypes are determined as indicated in the following table:
Table 3. Result data types with character string operands
One operand Other operand Data type of the result
Bit data Mixed, SBCS, or bit data Bit data
Mixed data Mixed or SBCS data Mixed data
SBCS data SBCS data SBCS data

Binary string operands

Binary strings are compatible with other binary strings. Binary strings include BINARY, VARBINARY, and BLOB.
Table 4. Result data types with binary string operands
One operand Other operand Data type of the result
BINARY(x) BINARY(y) BINARY(z) where z = max(x,y)
VARBINARY(x) BINARY(y) or VARBINARY(y) VARBINARY(z) where z = max(x,y)
BLOB(x) BINARY(y), VARBINARY(y), or BLOB(y) BLOB(z) where z = max(x,y)

Datetime operands

A date, time, or timestamp value is compatible with another value of the same type or any string expression that contains a valid string representation of the same type.

A DATE type is compatible with another DATE type or any string expression that contains a valid string representation of a date. A string representation is a value that is a built-in character string data type or graphic string data type. A string representation must not be a CLOB or DBCLOB and must have an actual length that is not greater than 255 bytes. The data type of the result is DATE.

A TIME type is compatible with another TIME type or any string expression that contains a valid string representation of a time. A string representation is a value that is a built-in character string data type or graphic string data type. A string representation must not be a CLOB or DBCLOB and must have an actual length that is not greater than 255 bytes. The data type of the result is TIME.

A timestamp type is compatible with another timestamp type, a timestamp constant, or any string expression that contains a valid string representation of a timestamp. A string representation is a value that is a built-in character string data type or graphic string data type. A string representation must not be a CLOB or DBCLOB and must have an actual length that is not greater than 255 bytes. The data type of the result is a timestamp as determined in the following table.

Table 5. Result data types with datetime operands
One operand Other operand Data type of the result
TIMESTAMP(x) WITHOUT TIME ZONE TIMESTAMP(y) WITHOUT TIME ZONE TIMESTAMP(max(x,y)) WITHOUT TIME ZONE
TIMESTAMP(x) WITHOUT TIME ZONE CHAR(y) or VARCHAR(y) TIMESTAMP(x) WITHOUT TIME ZONE 1
TIMESTAMP(x) WITH TIME ZONE TIMESTAMP(y) WITH TIME ZONE TIMESTAMP(max(x,y)) WITH TIME ZONE
TIMESTAMP(x) WITH TIME ZONE CHAR(y) or VARCHAR(y) TIMESTAMP(x) WITH TIME ZONE
TIMESTAMP(x) WITH TIME ZONE TIMESTAMP(y) WITHOUT TIME ZONE TIMESTAMP(max(x,y)) WITH TIME ZONE
Note: If one operand is TIMESTAMP(x) WITHOUT TIME ZONE and the other operand is CHAR(y) or VARCHAR(y), the result data type is TIMESTAMP(x) WITHOUT TIME ZONE even if the string representation contains a time zone.

If both operands are in the same encoding scheme, the result is in that encoding scheme. Otherwise the result is in the application encoding scheme.

Row ID operands

A row ID data type is compatible only with itself. The result has a row ID data type.

XML operands

XML data is compatible only with other XML data. The data type of the result is XML. Other data types can be treated as an XML data type by using the CAST specification or XMLPARSE functions to cast character, graphic, or binary data to XML data.

Distinct type operands

A distinct type is compatible only with itself. The data type of the result is the distinct type.