The data types of a result are determined by rules which are applied to the operands in an operation. This section explains those rules.
These rules apply to:
The data type of the result is determined by the data type of the operands. The data types of the first two operands determine an intermediate result data type, this data type and the data type of the next operand determine a new intermediate result data type, and so on. The last intermediate result data type and the data type of the last operand determine the data type of the result. For each pair of data types, the result data type is determined by the sequential application of the rules summarized in the following table:
If neither operand column allows nulls, the result does not allow nulls. Otherwise, the result allows nulls. If the description of any operand column is not the same as the description of the result, its values are converted to conform to the description of the result.
The conversion operation is exactly the same as if the values were assigned to the result. For example,
Binary strings (BLOBs) are compatible only with other binary strings
(BLOBs). The data type of the result is a BLOB. Other data types
can be treated as a BLOB data type by using the BLOB scalar function to cast
the data type to a BLOB. The length of the result BLOB is the largest
length of all the data types.
| If one operand column is... | And the other operand is... | The data type of the result column is... |
|---|---|---|
| BLOB(x) | BLOB(y) | BLOB(z) where z = max(x,y) |
Character and graphic strings are compatible with other character and
graphic strings as there is a defined conversion between their corresponding
CCSIDs.
| If one operand column is... | And the other operand is... | The data type of the result column is... |
|---|---|---|
| VARGRAPHIC(x) | VARGRAPHIC(y) or GRAPHIC(y) or VARCHAR(y) or CHAR(y) | VARGRAPHIC(z) where z = max(x,y) |
| GRAPHIC(x) | GRAPHIC(y) or CHAR(y) | GRAPHIC(z) where z = max(x,y) |
| UCS-2 data | DBCS or mixed or SBCS data | UCS-2 data |
| DBCS data | DBCS or mixed or SBCS data | DBCS data |
| VARCHAR(x) | VARCHAR(y) or CHAR(y) | VARCHAR(z) where z = max(x,y) |
| CHAR(x) | CHAR(y) | CHAR(z) where z = max(x,y) |
| bit data | mixed, SBCS, or bit data | bit data |
| mixed data | mixed or SBCS data | mixed data |
| SBCS data | SBCS data | SBCS data |
Numeric types are compatible only with other numeric types.
| If one operand column is... | And the other operand is... | The data type of the result column is... |
|---|---|---|
| FLOAT (double) | any numeric type | FLOAT (double) |
| FLOAT (single) | FLOAT (single) | FLOAT (single) |
| FLOAT (single) | DECIMAL, NUMERIC, BIGINT, INTEGER, or SMALLINT | FLOAT (double) |
| DECIMAL(w,x) | DECIMAL(y,z) or NUMERIC(y,z,) | DECIMAL(p,s) where p = min(31, max(x,z)+max(w-x,y-z)) s = max(x,z) |
| DECIMAL(w,x) | BIGINT | DECIMAL(p,x) where p = min(31, x+max(w-x,19)) |
| DECIMAL(w,x) | INTEGER | DECIMAL(p,x) where p = min(31, x+max(w-x,11)) |
| DECIMAL(w,x) | SMALLINT | DECIMAL(p,x) where p = min(31, x+max(w-x,5)) |
| NUMERIC(w,x) | NUMERIC(y,z) | NUMERIC(p,s) where p = min(31, max(x,z) + max(w-x, y-z)) s = max(x,z) |
| NUMERIC(w,x) | BIGINT | NUMERIC(p,x) where p = min(31, x + max(w-x,19)) |
| NUMERIC(w,x) | INTEGER | NUMERIC(p,x) where p = min(31, x + max(w-x,11)) |
| NUMERIC(w,x) | SMALLINT | NUMERIC(p,x) where p = min(31, x + max(w-x,5)) |
| BIGINT | BIGINT | BIGINT |
| BIGINT | INTEGER | BIGINT |
| BIGINT | SMALLINT | BIGINT |
| INTEGER | INTEGER | INTEGER |
| INTEGER | SMALLINT | INTEGER |
| SMALLINT | SMALLINT | SMALLINT |
| NONZERO SCALE BINARY | NONZERO SCALE BINARY | NONZERO SCALE BINARY (If either operand is nonzero scale binary, both operands must be binary with the same scale.) |
A DATE type is compatible with another DATE type, or any CHAR or VARCHAR expression that contains a valid string representation of a date. The data type of the result is DATE.
A TIME type is compatible with another TIME type, or any CHAR or VARCHAR expression that contains a valid string representation of a time. The data type of the result is TIME.
A TIMESTAMP type is compatible with another TIMESTAMP type, or any CHAR or
VARCHAR expression that contains a valid string representation of a
timestamp. The data type of the result is TIMESTAMP.
| If one operand column is... | And the other operand is... | The data type of the result column is... |
|---|---|---|
| DATE | DATE | DATE |
| TIME | TIME | TIME |
| TIMESTAMP | TIMESTAMP | TIMESTAMP |
A DataLink is compatible with another DataLink. However,
DataLinks with NO LINK CONTROL are only compatible with other DataLinks with
NO LINK CONTROL; DataLinks with FILE LINK CONTROL READ PERMISSION FS are
only compatible with other DataLinks with FILE LINK CONTROL READ PERMISSION
FS; and DataLinks with FILE LINK CONTROL READ PERMISSION DB are only
compatible with other DataLinks with FILE LINK CONTROL READ PERMISSION
DB. The data type of the result is DATALINK. The length of the
result DATALINK is the largest length of all the data types.
| If one operand column is... | And the other operand is... | The data type of the result column is... |
|---|---|---|
| DATALINK(x) | DATALINK(y) | DATALINK(z) where z = max(x,y) |
A distinct type is compatible only with itself. The data type of
the result is the distinct type.
| If one operand column is... | And the other operand is... | The data type of the result column is... |
|---|---|---|
| Distinct Type | Distinct Type | Distinct Type |