7.20.2 Data types of the results of value expressions

(1) If the data type of the value expression is character string data

• CASE expressions and the scalar functions COALESCE, DECODE, GREATEST, LEAST, LTDECODE, and NULLIF

  • If there is at least one value expression whose data type is VARCHAR, the data type of the result will be VARCHAR.

  • The data length of the result will be the data length of the value expression whose data length is longest.

• Columns derived as a result of a set operation, and columns derived by table value constructors

  • If all of the value expressions are CHAR type, and all the data lengths are the same, the data type of the result will be CHAR. Otherwise, the data type of the result will be VARCHAR.

  • The data length of the result will be the data length of the value expression whose data length is longest.

To Page Top

(2) If the data type of the value expression is numeric data

When the value expressions have numeric data types, the data type of the result is determined as shown in the following table.

Legend: N: An integer greater than or equal to 1

■ If the data type of the result is DECIMAL

The precision and scaling are determined as follows. Let value-expression-1 be DECIMAL(p1,s1), value-expression-2 be DECIMAL(p2,s2), and value-expression-N be DECIMAL(pN,sN).

• precision = MIN(38, Pmax + Smax)

• scaling = MIN(Smax, 38 - Pmax)

Pmax = MAX(p1-s1, p2 - s2, ..., pN - sN)

Smax = MAX(s1, s2, ..., sN)

If the data type of the value expression is INTEGER, use DECIMAL(20,0) for the calculation. If the data type of the value expression is SMALLINT, use DECIMAL(10,0) for the calculation.

Note that, if the numeric data of the result falls beyond the precision and scaling that are obtained here, the fractional part will be truncated. The following shows examples.

Example 1:

The SELECT statement below is executed with column C1 having type DECIMAL(37,0) and a value of NULL, and with column C2 having type DECIMAL(10,2) and a value of 12345678.12.

SELECT COALESCE("C1","C2") FROM "T1"

Retrieval results

12345678.1

In this case, the data type of the execution result of the scalar function COALESCE becomes DECIMAL(38,1), and the decimal digits beyond the scaling will be truncated.

Example 2:

Assume that you execute the following SELECT statement, which contains table DT that is derived by a table value constructor for which 1.1234567890123456789 and 10 are specified as row value constructor elements. In this case, literal 1.1234567890123456789 is treated as DECIMAL(20,19) type, and literal 10 is treated as INTEGER type. Because INTEGER type is treated as DECIMAL(20,0) type, the data type of the result of column C1 derived by the table value constructor will be DECIMAL(38,18). As a result, the decimal digits beyond the scaling will be truncated.

SELECT "C1" FROM (VALUES(1.1234567890123456789),(10)) "DT"("C1")

Retrieval results

1.123456789012345678
10.000000000000000000

To prevent truncation of the decimal digits beyond the scaling, you can explicitly specify literal 10 as a decimal literal (as DECIMAL type) as shown later. In this case, literal 1.1234567890123456789 is treated as DECIMAL(20,19) type, and literal 10.0 is treated as DECIMAL(3,1) type. Therefore, the data type of the result of column C1 derived by the table value constructor will be DECIMAL(21,19).

SELECT "C1" FROM (VALUES(1.1234567890123456789),(10.0)) "DT"("C1")

Retrieval results

1.1234567890123456789
10.0000000000000000000

To Page Top

(3) If the data type of the value expression is datetime data

When the value expressions have datetime data types, the data type of the result is determined as shown in the following table.

Legend:

N: An integer greater than or equal to 1

#

DATE type data is converted to TIMESTAMP type data by setting the time portion to 00:00:00.

■ When the fractional seconds precision is specified

When the fractional seconds precision is included in the results of value-expression-1 to value-expression-N, the fractional seconds precision of the result is determined as follows:

Letting the fractional seconds precision of value-expression-1 be p1, the fractional seconds precision of value-expression-2 be p2, and the fractional seconds precision of value-expression-N be pN, the fractional seconds precision of the result will be MAX(p1, p2, ..., pN).

The data length of the result depends on its fractional seconds precision.

To Page Top

(4) If the data type of the value expression is binary data

When the value expressions have binary data types, the data type of the result is determined as follows.

• CASE expressions, or the scalar functions COALESCE or NULLIF

  • The data type of the result will be VARBINARY.

  • The data length of the result will be the data length of the value expression whose data length is longest.

• Columns derived as a result of a set operation, columns derived by table value constructors, and scalar functions BITAND, BITOR, and BITXOR

  • If all the value expressions are BINARY type, and all the data lengths are the same, the data type of the result will be BINARY. Otherwise, the data type of the result will be VARBINARY.

  • The data length of the result will be the data length of the value expression whose data length is longest.

To Page Top