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 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. The LONG VARCHAR and LONG VARGRAPHIC data types continue to be supported but are deprecated and not recommended.
CHAR(2) UNION CHAR(4) UNION VARCHAR(3)
The
first pair results in a type of CHAR(4). The result values always
have 4 bytes. The final result type is VARCHAR(4). Values in the
result from the first UNION operation will always have a length of
4.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) |
CLOB(x) | CHAR(y), VARCHAR(y), or CLOB(y) | CLOB(z) where z = max(x,y) |
The code page of the result character string will be derived based on the rules for string conversions.
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) |
DBCLOB(x) | GRAPHIC(y), VARGRAPHIC(y), or DBCLOB(y) | DBCLOB(z) where z = max (x,y) |
The code page of the result graphic string will be derived based on the rules for string conversions.
In a Unicode database, a character string value is compatible with a graphic string value.
If one operand is... | And the other operand is... | The data type of the result is... |
---|---|---|
GRAPHIC(x) | CHAR(y) or GRAPHIC(y) | GRAPHIC(z) where z = max(x,y) |
VARGRAPHIC(x) | CHAR(y) or VARCHAR(y) | VARGRAPHIC(z) where z = max(x,y) |
VARCHAR(x) | GRAPHIC(y) or VARGRAPHIC | VARGRAPHIC(z) where z = max(x,y) |
DBCLOB(x) | CHAR(y) or VARCHAR(y) or CLOB(y) | DBCLOB(z) where z = max(x,y) |
CLOB(x) | GRAPHIC(y) or VARGRAPHIC(y) | DBCLOB(z) where z = max(x,y) |
A binary string (BLOB) value is compatible only with another binary string (BLOB) value. The BLOB scalar function can be used to cast from other types if they should be treated as BLOB types. The length of the result BLOB is the largest length of all the data types.
Numeric types are compatible with other numeric data types, character-string data types (except CLOB), and in a Unicode database, graphic-string data types (except DBCLOB). Numeric types include SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, and DECFLOAT.
If one operand is... | And the other operand is... | The data type of the result is... |
---|---|---|
SMALLINT | SMALLINT | SMALLINT |
SMALLINT | String | DECFLOAT(34) |
INTEGER | SMALLINT or INTEGER | INTEGER |
INTEGER | String | DECFLOAT(34) |
BIGINT | SMALLINT, INTEGER, or BIGINT | BIGINT |
BIGINT | String | DECFLOAT(34) |
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)1 s = max(x,z) |
DECIMAL(w,x) | String | DECFLOAT(34) |
REAL | REAL | REAL |
REAL | SMALLINT, INTEGER, BIGINT, or DECIMAL | DOUBLE |
REAL | String | DECFLOAT(34) |
DOUBLE | SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, or DOUBLE | DOUBLE |
DOUBLE | String | DECFLOAT(34) |
DECFLOAT(n) | SMALLINT, INTEGER, DECIMAL (<=16,s), REAL, or DOUBLE | DECFLOAT(n) |
DECFLOAT(n) | BIGINT or DECIMAL (>16,s) | DECFLOAT(34) |
DECFLOAT(n) | DECFLOAT(m) | DECFLOAT(MAX(n,m)) |
DECFLOAT(n) | String | DECFLOAT(34) |
1 Precision cannot exceed 31. |
Datetime data types are compatible with other operands of the same data type or any CHAR or VARCHAR expression that contains a valid string representation of the same data type. In addition, DATE is compatible with TIMESTAMP and the other operand of a TIMESTAMP can be the string representation of a timestamp or a date. In a Unicode database, character and graphic strings are compatible which implies that GRAPHIC or VARGRAPHIC string representations of datetime values are compatible with other datetime operands.
If one operand is... | And the other operand is... | The data type of the result is... |
---|---|---|
DATE | DATE, CHAR(y), or VARCHAR(y) | DATE |
TIME | TIME, CHAR(y), or VARCHAR(y) | TIME |
TIMESTAMP(x) | TIMESTAMP(y) | TIMESTAMP(max(x,y)) |
TIMESTAMP(x) | DATE, CHAR(y), or VARCHAR(y) | TIMESTAMP(x) |
An XML value is compatible with another XML value. The data type of the result is XML.
A Boolean value is compatible with another Boolean value. The data type of the result is BOOLEAN.
A user-defined distinct type value is compatible only with another value of the same user-defined distinct type. The data type of the result is the user-defined distinct type.
A reference type value is compatible with another value of the same 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 value is compatible with another value of the same 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.
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.