8.15.1 DECODE
Compares the values in the target data and the comparison data one at a time, and if there is a match, returns the corresponding value as the return value. If no match is found between the target data and comparison data, returns the predefined return value.
When multiple comparison data items are specified, it returns the return value corresponding to the first comparison data item that is matched.
- Organization of this subsection
(1) Specification format
scalar-function-DECODE ::= DECODE(target-data,comparison-data,return-value
[,comparison-data,return-value]...
[,predefined-return-value])
target-data ::= {value-expression | NULL}
comparison-data ::= {value-expression | NULL}
return-value ::= {value-expression | NULL}
predefined-return-value ::= {value-expression | NULL}
(2) Explanation of specification format
- target-data:
-
Specifies the target data. Specify the target data in the form of a value expression, or as NULL. For details about value expressions, see 7.20 Value expression.
- comparison-data:
-
Specifies the comparison data.
The following rules apply:
-
Specify the comparison data in the form of a value expression, or as NULL. For details about value expressions, see 7.20 Value expression.
-
The first specification of comparison-data cannot be NULL.
-
The first specification of comparison-data cannot be a dynamic parameter by itself.
-
If you specify a dynamic parameter by itself for the second or subsequent specification of comparison-data, the data type of the dynamic parameter is assumed to be the data type of the first comparison-data.
-
- return-value:
-
Specifies the value to return when the target data matches a comparison data item.
The following rules apply:
-
Specify the return value in the form of a value expression, or as NULL. For details about value expressions, see 7.20 Value expression.
-
The first-specified return value cannot be NULL.
-
The first-specified return value cannot be a dynamic parameter by itself.
-
If you specify a dynamic parameter by itself for the second or subsequent return value, the data type of the dynamic parameter is assumed to be the data type of the first return value.
-
- predefined-return-value:
-
Specifies a predefined value to return when the target data does not match any of the comparison data. If predefined-return-value is omitted, NULL is assumed.
The following rules apply:
-
Specify the predefined return value in the form of a value expression, or as NULL. For details about value expressions, see 7.20 Value expression.
-
If you specify a dynamic parameter by itself for predefined-return-value, the data type of the dynamic parameter is assumed to be the data type of the first return value.
-
(3) Rules
-
You must specify numeric data, character string data, or datetime data for target-data, comparison-data, return-value, and predefined-return-value.
-
When NULL is specified for target-data, comparison-data, return-value, or predefined-return-value, it denotes the null value.
-
You must specify data types that can be compared (except when specifying NULL) for target-data and comparison-data. For details about data types that can be compared, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared.
Note, however, that if target-data and comparison-data are character string data or datetime data, specify a combination of data types based on the following table.
Table 8‒59: Combinations of data types that can be specified for the target data and the comparison data for the scalar function DECODE Target data
Comparison data
Character string data
Datetime data
Character string literal that is the predefined input representation for date data
Character string literal that is the predefined input representation for time data
Character string literal that is the predefined input representation for time stamp data
Other data
Date data
Time data
Time stamp data
Character string data
Character string literal that is the predefined input representation for date data
Y
Y
Y
Y
N
N
N
Character string literal that is the predefined input representation for time data
Y
Y
Y
Y
N
N
N
Character string literal that is the predefined input representation for time stamp data
Y
Y
Y
Y
N
N
N
Other data
Y
Y
Y
Y
N
N
N
Datetime data
Date data
Y
N
Y
N
Y
N
Y
Time data
N
Y
N
N
N
Y
N
Time stamp data
Y
N
Y
N
Y
N
Y
- Legend:
-
Y: Can be specified.
N: Cannot be specified.
-
You must specify data types that can be compared (except when specifying NULL) for return-value and predefined-return-value. For details about data types that can be compared, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared.
However, if return-value and predefined-return-value are character string data or datetime data, specify a combination of data types based on the following table.
Table 8‒60: Combinations of data types that can be specified for the return value and the predefined return value for the scalar function DECODE Return value
Predefined return value, or return value#
Character string data
Datetime data
Character string literal that is the predefined input representation for date data
Character string literal that is the predefined input representation for time data
Character string literal that is the predefined input representation for time stamp data
Other data
Date data
Time data
Time stamp data
Character string data
Character string literal that is the predefined input representation for date data
Y
Y
Y
Y
N
N
N
Character string literal that is the predefined input representation for time data
Y
Y
Y
Y
N
N
N
Character string literal that is the predefined input representation for time stamp data
Y
Y
Y
Y
N
N
N
Other data
Y
Y
Y
Y
N
N
N
Datetime data
Date data
N
N
N
N
Y
N
Y
Time data
N
N
N
N
N
Y
N
Time stamp data
N
N
N
N
Y
N
Y
- Legend:
-
Y: Can be specified.
N: Cannot be specified.
- #
-
If multiple return-value items are specified and all return-value items are character string data or datetime data, the combinations of data types that can be specified for each return-value item are as shown in Table 8‒60: Combinations of data types that can be specified for the return value and the predefined return value for the scalar function DECODE.
-
You can specify a maximum of 127 comparison-data and return-value pairs.
-
The data type and data length of the execution result depends on the data types of the results of return-value and predefined-return-value, and is determined according to the rules described in 7.20.2 Data types of the results of value expressions.
Note that the specification of NULL for return-value and predefined-return-value does not affect the data type and data length of the execution result.
-
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
-
If the target data is the null value and NULL is specified for one of the comparison data items, the return value associated with that item is returned.
(4) Examples
- Example 1:
-
Convert the abbreviations of country names in column C2 from table T1 as follows:
-
JPN → Japan
-
IND → India
-
Null value → NODATA
-
Other → Other
SELECT "C1",DECODE("C2",'JPN','Japan','IND','India',NULL,'NODATA','Other') FROM "T1"![[Figure]](GRAPHICS/F0000332.GIF)
-
- Example 2:
-
Search the employee table (EMPLIST) as follows:
-
Determine the number of males and females in each section (SCODE)
SELECT "SCODE",SUM(DECODE("SEX",'M',1,0)) AS "Men", SUM(DECODE("SEX",'F',1,0)) AS "Women" FROM "EMPLIST" GROUP BY "SCODE"![[Figure]](GRAPHICS/F0000333.GIF)
-