IBM Books

SQL Reference


Rules for Result Data Types

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

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)

The code page of the result character string will be derived based on the Rules for String Conversions.

Graphic Strings

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)

The code page of the result graphic string will be derived based on the Rules for String Conversions.

Binary Large Object (BLOB)

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

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
Note:

  1. Precision cannot exceed 31.

DATE

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.

TIME

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.

TIMESTAMP

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.

DATALINK

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.

User-defined Types

Distinct 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.

Reference Types

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.

Nullable Attribute of Result

With the exception of INTERSECT and EXCEPT, the result allows nulls unless both operands do not allow nulls.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]