7.20.2 Data types of the results of value expressions
The data type of the result is determined by the data types of the specified value expressions for CASE expressions, columns derived from the results of set operations, columns derived by table value constructors, and the following scalar functions:
-
COALESCE
-
DECODE
-
GREATEST
-
LEAST
-
LTDECODE
-
NULLIF
-
BITAND
-
BITOR
-
BITXOR
- Example 1: CASE expression
-
The data type of the result of the CASE expression is determined by the data types of value expressions 1 through 3.
- Example 2: Scalar function GREATEST
-
The data type of the result of the scalar function GREATEST is determined by the data types of value expressions 1 through 3.
This section describes the rules for determining data type of the result of a value expression.
- Organization of this subsection
(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.
-
(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.
No. |
Data type of value expression N |
Data type of value expression N + 1 |
Data type of the result |
---|---|---|---|
1 |
SMALLINT |
SMALLINT |
SMALLINT |
2 |
INTEGER |
INTEGER |
|
3 |
DECIMAL |
DECIMAL |
|
4 |
DOUBLE PRECISION |
DOUBLE PRECISION |
|
5 |
INTEGER |
SMALLINT |
INTEGER |
6 |
INTEGER |
||
7 |
DECIMAL |
DECIMAL |
|
8 |
DOUBLE PRECISION |
DOUBLE PRECISION |
|
9 |
DECIMAL |
SMALLINT |
DECIMAL |
10 |
INTEGER |
||
11 |
DECIMAL |
||
12 |
DOUBLE PRECISION |
DOUBLE PRECISION |
|
13 |
DOUBLE PRECISION |
SMALLINT |
DOUBLE PRECISION |
14 |
INTEGER |
||
15 |
DECIMAL |
||
16 |
DOUBLE PRECISION |
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
-
(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.
No. |
Data type of value expression N |
Data type of value expression N + 1 |
Data type of the result |
---|---|---|---|
1 |
DATE |
DATE |
DATE |
2 |
TIMESTAMP |
TIMESTAMP# |
|
3 |
TIME |
TIME |
TIME |
4 |
TIMESTAMP |
DATE |
TIMESTAMP# |
5 |
TIMESTAMP |
TIMESTAMP |
- 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.
(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.
-