SQL Reference

Promotion of Data Types

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 (see Function Resolution)
• casting user-defined types (see Casting Between Data Types)
• assigning user-defined types to built-in data types (see User-defined Type Assignments).

Table 5 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 5. Data Type Precedence Table

Data Type	Data Type Precedence List (in best-to-worst order)
CHAR	CHAR, VARCHAR, LONG VARCHAR, CLOB
VARCHAR	VARCHAR, LONG VARCHAR, CLOB
LONG VARCHAR	LONG VARCHAR, CLOB
GRAPHIC	GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, DBCLOB
VARGRAPHIC	VARGRAPHIC, LONG VARGRAPHIC, DBCLOB
LONG VARGRAPHIC	LONG VARGRAPHIC, DBCLOB
BLOB	BLOB
CLOB	CLOB
DBCLOB	DBCLOB
SMALLINT	SMALLINT, INTEGER, BIGINT, decimal, real, double
INTEGER	INTEGER, BIGINT, decimal, real, double
BIGINT	BIGINT, decimal, real, double
decimal	decimal, real, double
real	real, double
double	double
DATE	DATE
TIME	TIME
TIMESTAMP	TIMESTAMP
DATALINK	DATALINK
udt	udt (same name) or a supertype of udt
REF(T)	REF(S) (provided that S is a supertype of T)
Note: The lower case types above 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 Shorter and longer form synonyms of the data types listed are considered to be the same as the synonym listed.