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.
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:
|
Character and graphic 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) |
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
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.
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.