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:
These rules are applied subject to other restrictions on long strings for the various operations.
The rules involving various data types follow. In some cases, a table is used to show the possible result data types.
These tables identify the data type of the result, including the applicable length or precision and scale. The result type is determined by considering the operands. If there is more than one pair of operands, start by considering the first pair. This gives a result type which is considered 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 for the operation. Processing of operations is done from left to right so that the intermediate result types are important when operations are repeated. For example, consider a situation involving:
CHAR(2) UNION CHAR(4) UNION VARCHAR(3)
The first pair results in a type of CHAR(4). The result values always have 4 characters. The final result type is VARCHAR(4). Values in the result from the first UNION operation will always have a length of 4.
Character strings are compatible with other character strings.
Character strings include data types CHAR, VARCHAR, LONG VARCHAR, and
CLOB.
| If one operand is... | And the other operand is... | The data type of the result is... |
|---|---|---|
| CHAR(x) | CHAR(y) | CHAR(z) where z = max(x,y) |
| CHAR(x) | VARCHAR(y) | VARCHAR(z) where z = max(x,y) |
| VARCHAR(x) | CHAR(y) or VARCHAR(y) | VARCHAR(z) where z = max(x,y) |
| LONG VARCHAR | CHAR(y), VARCHAR(y), or LONG VARCHAR | LONG VARCHAR |
| CLOB(x) | CHAR(y), VARCHAR(y), or CLOB(y) | CLOB(z) where z = max(x,y) |
| CLOB(x) | LONG VARCHAR | CLOB(z) where z = max(x,32700) |
The code page of the result character string will be derived based on the Rules for String Conversions.
Graphic strings are compatible with other graphic strings.
Graphic strings include data types GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, and
DBCLOB.
| If one operand is... | And the other operand is... | The data type of the result is... |
|---|---|---|
| GRAPHIC(x) | GRAPHIC(y) | GRAPHIC(z) where z = max(x,y) |
| VARGRAPHIC(x) | GRAPHIC(y) OR VARGRAPHIC(y) | VARGRAPHIC(z) where z = max(x,y) |
| LONG VARGRAPHIC | GRAPHIC(y), VARGRAPHIC(y), or LONG VARGRAPHIC | LONG VARGRAPHIC |
| DBCLOB(x) | GRAPHIC(y), VARGRAPHIC(y), or DBCLOB(y) | DBCLOB(z) where z = max (x,y) |
| DBCLOB(x) | LONG VARGRAPHIC | DBCLOB(z) where z = max (x,16350) |
The code page of the result graphic string will be derived based on the Rules for String Conversions.
A BLOB is compatible only with another BLOB and the result is a BLOB. The BLOB scalar function should be used to cast from other types if they should be treated as BLOB types (see BLOB). The length of the result BLOB is the largest length of all the data types.
Numeric types are compatible with other numeric types. Numeric
types include SMALLINT, INTEGER, BIGINT, DECIMAL, REAL and DOUBLE.
| If one operand is... | And the other operand is... | The data type of the result is... | ||
|---|---|---|---|---|
| SMALLINT | SMALLINT | SMALLINT | ||
| INTEGER | INTEGER | INTEGER | ||
| INTEGER | SMALLINT | INTEGER | ||
| BIGINT | BIGINT | BIGINT | ||
| BIGINT | INTEGER | BIGINT | ||
| BIGINT | SMALLINT | 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 | ||
| DOUBLE | any numeric | DOUBLE | ||
| ||||
A date is compatible with another date, 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 is compatible with another time, 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 is compatible with another timestamp, or any CHAR or VARCHAR expression that contains a valid string representation of a timestamp. The data type of the result is TIMESTAMP.
A datalink is compatible with another datalink. The data type of the result is DATALINK. The length of the result DATALINK is the largest length of all the data types.
A user-defined distinct type is compatible only with the same user-defined distinct type. The data type of the result is the user-defined distinct type.
A reference type is compatible with another reference type provided that their target types have a common supertype. The data type of the result is a reference type having the common supertype as the target type. If all operands have the identical scope table, the result has that scope table. Otherwise the result is unscoped.
A structured type is compatible with another structured type provided that they have a common supertype. The static data type of the resulting structured type column is the structured type that is the least common supertype of either column.
For example, consider the following structured type hierarchy,
A
/ \
B C
/ \
D E
/ \
F G
Structured types of the static type E and F are compatible with the resulting static type of B, which is the least common super type of E and F.
With the exception of INTERSECT and EXCEPT, the result allows nulls unless both operands do not allow nulls.