DB2 Data types - Rules for result data types

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 topic explains those rules.

These rules apply to:

Corresponding columns in fullselects of set operations (UNION, INTERSECT and EXCEPT)
Result expressions of a CASE expression and the DECODE and NVL2 scalar functions
Arguments of the scalar function COALESCE (also NVL and VALUE)
Arguments of the scalar functions GREATEST, LEAST, MAX, and MIN
Expression values of the in list of an IN predicate
Corresponding expressions of a multiple row VALUES clause
Expression values for the elements in an array constructor
Arguments of a BETWEEN predicate (except if the data types of all operands are numeric)
Arguments for the aggregation group ranges in OLAP specifications

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.

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

A character string value is compatible with another character string value.
Character strings include data types CHAR, 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)
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)

Graphic strings

A graphic string value is compatible with another graphic string value.
Graphic strings include data types GRAPHIC, 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)
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, if either operand has string units CODEUNITS32 and the derived code page is not 0, the string units of the result character string is CODEUNITS32.
If an operand is defined with CODEUNITS32, the other operand cannot be defined as FOR BIT DATA.
Otherwise, the string units of the result character string is OCTETS.
Special cases apply when the string units of one operand is CODEUNITS32 and the string units of the other operand is OCTETS with a length attribute that exceeds the data type maximum in CODEUNITS32.
In a Unicode database, if either operand has string units CODEUNITS32, or CODEUNITS16, the string units of the result graphic string is CODEUNITS32.
Special cases apply when the string units of one operand is CODEUNITS32 and the string units of the other operand is CODEUNITS16 with a length attribute that exceeds the data type maximum in CODEUNITS32.

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)

Character and graphic strings in a Unicode database

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)

The string units of the result graphic string will be CODEUNITS32 if either operand has string units CODEUNITS32, or CODEUNITS16.
Special cases apply when the string units of one operand is CODEUNITS32 and the string units of the other operand is OCTETS or CODEUNITS16 with a length attribute that exceeds the data type maximum in CODEUNITS32.

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)

Binary strings

Binary strings are compatible with other binary strings and FOR BIT DATA character strings.
Binary strings include BINARY, VARBINARY and BLOB.

Table 1. Operands and the resulting data type

If one operand is...	And the other operand is...	The data type of the result is...
BINARY(x)	BINARY(y) or CHAR(y) FOR BIT DATA	BINARY(z) where z=max(x,y)
VARBINARY(x)	BINARY(y), VARBINARY(y), CHAR(y) FOR BIT DATA, or VARCHAR(y) FOR BIT DATA	VARBINARY(z) where z=max(x,y)
VARCHAR(x) FOR BIT DATA	BINARY(y) or VARBINARY(y)	VARBINARY(z) where z=max(x,y)
BLOB(x)	BINARY(y), VARBINARY(y), BLOB(y), CHAR(y) FOR BIT DATA, or VARCHAR(y) FOR BIT DATA	BLOB(z) where z=max(x,y)

Numeric

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.

Table 2. Operands and the resulting data type

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)¹
DECIMAL(w,x)	INTEGER	DECIMAL(p,x) where p = x+max(w-x,11)¹
DECIMAL(w,x)	BIGINT	DECIMAL(p,x) where p = x+max(w-x,19)¹
DECIMAL(w,x)	DECIMAL(y,z)	DECIMAL(p,s) where p = max(x,z)+max(w-x,y-z)¹ 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)
¹ Precision cannot exceed 31.

Datetime

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.

Table 3. Result data types with 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)

XML

An XML value is compatible with another XML value. The data type of the result is XML.

Boolean

A Boolean value is compatible with another Boolean value.
The data type of the result is BOOLEAN.

User-defined types

Distinct types: A strongly typed distinct type value is compatible only with another value of the same distinct type.
The data type of the result is the distinct type.
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.
Array data types: A user-defined array data type value is compatible only with another value of the same user-defined array data type.
The data type of the result is the user-defined array data type.
Cursor data types: A CURSOR value is compatible with another CURSOR value.
The result data type is CURSOR.
A user-defined cursor data type value is compatible only with another value of the same user-defined cursor data type.
The data type of the result is the user-defined cursor data type.
Row data types: A user-defined row data type value is compatible only with another value of the same user-defined row data type.
The data type of the result is the user-defined row data type.

Reference types

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.

Structured types

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.

For example, consider the following structured type hierarchy,

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.

Nullable attribute of result

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

For INTERSECT, if either operand does not allow nulls the result does not allow nulls (the intersection would never be null).
For EXCEPT, if the first operand does not allow nulls the result does not allow nulls (the result can only be values from the first operand).