The data types of a result are determined by rules which are applied to the operands in an operation. This topic 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... |
---|---|---|
CHAR(x OCTETS) with x>63 | CHAR(y CODEUNITS32) | VARCHAR(z CODEUNITS32) where z = max(x,y) |
VARCHAR(x OCTETS) with x>8168 | CHAR(y CODEUNITS32) or VARCHAR(y CODEUNITS32) | Error |
CLOB(x OCTETS) with x>536870911 | CHAR(y CODEUNITS32), VARCHAR(y CODEUNITS32), or CLOB(y CODEUNITS32) | CLOB(536870911 CODEUNITS32) |
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.
If one operand is... | And the other operand is... | The data type of the result is... |
---|---|---|
GRAPHIC(x CODEUNITS16) with x>63 | GRAPHIC(y CODEUNITS32) | VARGRAPHIC(z CODEUNITS32) where z = max(x,y) |
VARGRAPHIC(x CODEUNITS16 ) with x>8168 | GRAPHIC(y CODEUNITS32) or VARGRAPHIC(y CODEUNITS32) | Error |
DBCLOB(x CODEUNITS16 ) with x>536870911 | GRAPHIC(y CODEUNITS32), VARGRAPHIC(y CODEUNITS32) or DBCLOB(y CODEUNITS32) | DBCLOB(536870911 CODEUNITS32) |
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) |
If one operand is... | And the other operand is... | The data type of the result is... |
---|---|---|
CHAR(x OCTETS) with x>63 | GRAPHIC(y CODEUNITS32) | VARGRAPHIC(z CODEUNITS32) where z = max(x,y) |
GRAPHIC(x CODEUNITS16) with x>63 | CHAR(y CODEUNITS32) | VARGRAPHIC(z CODEUNITS32) where z = max(x,y) |
VARCHAR(x OCTETS ) with x>8168 | GRAPHIC(y CODEUNITS32) or VARGRAPHIC(y CODEUNITS32) | Error |
VARGRAPHIC(x CODEUNITS16 ) with x>8168 | CHAR(y CODEUNITS32) or VARCHAR(y CODEUNITS32) | Error |
CLOB(x OCTETS) with x>536870911 | GRAPHIC(y CODEUNITS32), VARGRAPHIC(y CODEUNITS32), or DBCLOB(y CODEUNITS32) | DBCLOB(536870911 CODEUNITS32) |
DBCLOB(x CODEUNITS16 ) with x>536870911 | CHAR(y CODEUNITS32), VARCHAR(y CODEUNITS32), or CLOB(y CODEUNITS32) | DBCLOB(536 870 911 CODEUNITS32) |
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.
If both operands have the same weakly typed distinct type, the result is the distinct type. Otherwise, if any operand is a weakly typed distinct type then the data type of the operand is considered to be the source data type and the result data type is determined based on the combination of built-in data type operands.
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.