Data
types can be classified into groups of related data types. Within
such groups, a precedence order exists where one data type is considered
to precede another data type. This precedence is used to allow the promotion of
one data type to a data type later in the precedence ordering.
For example, the data type CHAR can be promoted to VARCHAR;
INTEGER can be promoted to DOUBLE-PRECISION; but CLOB is NOT promotable
to VARCHAR.
Promotion of data types is used when:
- Performing function resolution
- Casting user-defined types
- Assigning user-defined types to built-in data types
Table 1 shows the precedence
list (in order) for each data type and can be used to determine the
data types to which a given data type can be promoted. The table
shows that the best choice is always the same data type instead of
choosing to promote to another data type.
Table 1. Data Type Precedence
Table
| Data Type |
Data Type Precedence List (in best-to-worst order) |
| SMALLINT |
SMALLINT, INTEGER, BIGINT, decimal, real, double,
DECFLOAT |
| INTEGER |
INTEGER, BIGINT, decimal, real, double,
DECFLOAT |
| BIGINT |
BIGINT, decimal, real, double, DECFLOAT |
| decimal |
decimal, real, double, DECFLOAT |
| real |
real, double, DECFLOAT |
| double |
double, DECFLOAT |
| DECFLOAT |
DECFLOAT |
| CHAR |
CHAR, VARCHAR, CLOB |
| VARCHAR |
VARCHAR, CLOB |
| CLOB |
CLOB |
| GRAPHIC |
GRAPHIC, VARGRAPHIC, DBCLOB |
| VARGRAPHIC |
VARGRAPHIC, DBCLOB |
| DBCLOB |
DBCLOB |
| BINARY |
BINARY,
VARBINARY, BLOB |
| VARBINARY |
VARBINARY,
BLOB |
| BLOB |
BLOB |
| DATE |
DATE, TIMESTAMP |
| TIME |
TIME |
| TIMESTAMP |
TIMESTAMP |
| BOOLEAN |
BOOLEAN |
| CURSOR |
CURSOR |
| ARRAY |
ARRAY |
| udt |
udt (same name) or a supertype of udt |
| REF(T) |
REF(S) (provided that S is a supertype of T) |
| ROW |
ROW |
Note:
- The lowercase types in the preceding table are defined as follows:
- decimal = DECIMAL(p,s) or NUMERIC(p,s)
- real = REAL or FLOAT(n), where n is
not greater than 24
- double = DOUBLE, DOUBLE-PRECISION, FLOAT or FLOAT(n),
where n is greater than 24
- udt = a user-defined type (except for a
weakly typed distinct type which uses the source type to determine
data type precedence)
Shorter and longer form synonyms of the listed data types
are considered to be the same as the listed form.
- For a Unicode database, the following data types are considered
to be equivalent:
- CHAR and GRAPHIC
- VARCHAR and VARGRAPHIC
- CLOB and DBCLOB
When resolving a function within a Unicode database, if a
user-defined function and a built-in function are both applicable
for a given function invocation, then generally the built-in function
will be invoked. The UDF will be invoked only if its schema precedes
SYSIBM in the CURRENT PATH special register and
if its argument data types match all the function invocation argument
data types, regardless of Unicode data type equivalence.
|