7.21.2 Data types of the results of value expressions
The data type of the result is determined by the data type of the specified value expression in the following place.
-
CASE expression
-
Column that is derived as a result of a set operation
-
Column that is derived by a table value constructor
-
Column that is derived by a table function derived table that specifies the ADB_GENERATE_SERIES function
-
Scalar functions COALESCE, DECODE, GREATEST, LEAST, LTDECODE, NULLIF, BITAND, BITOR, and BITXOR
-
Window functions LAG and LEAD
- Example 1: CASE expression
-
![[Figure]](GRAPHICS/F0000178.GIF)
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
-
![[Figure]](GRAPHICS/F0000179.GIF)
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, scalar functions COALESCE, DECODE, GREATEST, LEAST, LTDECODE, NULLIF, and window functions LAG, LEAD
-
The data type of the result of a CASE expression or the above function will be of type VARCHAR.
-
The data length of the result of a CASE expression or the above function will be the same as the longest data length in the specified value expression.
However, if at least one of the value expressions specified in the CASE expression or the function above has a data type of STRING, the data type of the result of the CASE expression or the function above will be STRING, and the data length of the result will be 32,000,000 bytes.
-
-
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.
However, if there is at least one value expression with a data type of STRING, the resulting data type will be STRING, and the resulting data length will be 32,000,000 bytes.
-
(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.
|
Data type of value expression N |
Data type of value expression N + 1 |
Data type of the result |
|---|---|---|
|
BIGINT |
BIGINT |
BIGINT#1 |
|
INTEGER |
||
|
SMALLINT |
||
|
DECIMAL |
DECIMAL |
|
|
NUMERIC |
||
|
DOUBLE PRECISION |
DOUBLE PRECISION |
|
|
FLOAT |
||
|
REAL |
||
|
INTEGER |
BIGINT |
BIGINT#1 |
|
INTEGER |
INTEGER |
|
|
SMALLINT |
||
|
DECIMAL |
DECIMAL |
|
|
NUMERIC |
||
|
DOUBLE PRECISION |
DOUBLE PRECISION |
|
|
FLOAT |
||
|
REAL |
REAL#2 |
|
|
SMALLINT |
BIGINT |
BIGINT#1 |
|
INTEGER |
INTEGER |
|
|
SMALLINT |
SMALLINT |
|
|
DECIMAL |
DECIMAL |
|
|
NUMERIC |
||
|
DOUBLE PRECISION |
DOUBLE PRECISION |
|
|
FLOAT |
||
|
REAL |
REAL |
|
|
DECIMAL, NUMERIC |
BIGINT |
DECIMAL |
|
INTEGER |
||
|
SMALLINT |
||
|
DECIMAL |
||
|
NUMERIC |
||
|
DOUBLE PRECISION |
DOUBLE PRECISION |
|
|
FLOAT |
||
|
REAL |
||
|
DOUBLE PRECISION, FLOAT |
BIGINT |
DOUBLE PRECISION |
|
INTEGER |
||
|
SMALLINT |
||
|
DECIMAL |
||
|
NUMERIC |
||
|
DOUBLE PRECISION |
||
|
FLOAT |
||
|
REAL |
||
|
REAL |
BIGINT |
DOUBLE PRECISION |
|
INTEGER |
REAL#2 |
|
|
SMALLINT |
REAL |
|
|
DECIMAL |
DOUBLE PRECISION |
|
|
NUMERIC |
||
|
DOUBLE PRECISION |
||
|
FLOAT |
||
|
REAL |
REAL |
Legend: N: An integer greater than or equal to 1
- #1
-
If the integer data type format is a legacy format, it will be an INTEGER type.
- #2
-
If the integer data type format is a legacy format, it will be a DOUBLE PRECISION type.
- ■ 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 BIGINT, calculate as DECIMAL(20,0); if it is INTEGER, calculate as DECIMAL(10,0); if it is SMALLINT, calculate as DECIMAL(5,0).#
- #
-
If the integer data type format is in a legacy format, calculate as DECIMAL(20,0) when the data type of the value expression is BIGINT or INTEGER type, or DECIMAL(10,0) when the data type is SMALLINT type.
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 BIGINT type#. Because BIGINT 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
- #
-
If the integer data type format is a legacy format, it will be an INTEGER type.
-
(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 WITHOUT TIME ZONE |
TIMESTAMP WITHOUT TIME ZONE# |
|
|
3 |
TIME |
TIME |
TIME |
|
4 |
TIMESTAMP WITHOUT TIME ZONE |
DATE |
TIMESTAMP WITHOUT TIME ZONE# |
|
5 |
TIMESTAMP WITHOUT TIME ZONE |
TIMESTAMP WITHOUT TIME ZONE |
|
|
6 |
TIMESTAMP WITH TIME ZONE |
TIMESTAMP WITH TIME ZONE |
TIMESTAMP WITH TIME ZONE |
- Legend:
-
N: An integer greater than or equal to 1
- #
-
DATE type data is converted to TIMESTAMP WITHOUT TIME ZONE 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, scalar functions COALESCE, NULLIF, or window functions LAG and LEAD
-
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.
-
(5) If the data type of the value expression is logical data
When the value expressions have logical data types, the data type of the result is determined as follows:
-
CASE expressions, scalar functions COALESCE, DECODE, GREATEST, LEAST, LTDECODE, NULLIF, and window functions LAG, LEAD
The data type of the result is BOOLEAN type.
-
Columns derived as a result of a set operation, and columns derived by table value constructors
The data type of the result is BOOLEAN type.
(6) If the data type of the value expression is UUID data
When the value expressions have UUID data types, the data type of the result is determined as follows:
-
CASE expressions, scalar functions COALESCE, DECODE, GREATEST, LEAST, LTDECODE, NULLIF, and window functions LAG, LEAD
The data type of the result is UUID type.
-
Columns derived as a result of a set operation, and columns derived by table value constructors
The data type of the result is UUID type.