8.15.4 LTDECODE

Compares the values in the target data and in the comparison data one at a time, and, if any value in the target data is less than the value in the comparison data, returns the corresponding return value. If no value in the target data is less than any of the values in the comparison data, this function returns the predefined return value.

If multiple comparison data items are specified, the function returns the return value that corresponds to the first comparison data item whose value is greater than the value in the target data.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Rules
(4) Examples

(1) Specification format

scalar-function-LTDECODE ::= LTDECODE(target-data,comparison-data,return-value
                        [,comparison-data,return-value]...
                        [,predefined-return-value])
 
  target-data ::= value-expression
  comparison-data ::= value-expression
  return-value ::= {value-expression | NULL}
  predefined-return-value ::= {value-expression | NULL}

To Page Top

(2) Explanation of specification format

target-data:

Specifies the target data. Specify the target data in the form of a value expression. For details about value expressions, see 7.20 Value expression.

comparison-data:

Specifies the comparison data. Specify the comparison data in the form of a value expression. For details about value expressions, see 7.20 Value expression.

return-value:

Specifies the value to be returned if the value of the target data is less than the value of the comparison data. Specify the return value in the form of a value expression, or as NULL. For details about value expressions, see 7.20 Value expression.

predefined-return-value:

Specifies the predefined value to be returned if the value of the target data is equal to or greater than any of the values of the comparison data. 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.

Note that if predefined-return-value is omitted, NULL is assumed.

To Page Top

(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 return-value or predefined-return-value, it denotes the null value.

You must specify data types that can be compared 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‒61: Combinations of data types that can be specified for the target data and the comparison data for the scalar function LTDECODE
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‒62: Combinations of data types that can be specified for the return value and the predefined return value for the scalar function LTDECODE
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‒62: Combinations of data types that can be specified for the return value and the predefined return value for the scalar function LTDECODE.

You can specify a maximum of 256 target-data, comparison-data, return-value, and predefined-return-value items in total.
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 following specification for return-value and predefined-return-value does not affect the data type and data length of the execution result.
- The specification of only a single dynamic parameter
- NULL
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
You must specify comparison-data and return-value as a set. The return-value that corresponds to comparison-data is the value to be specified following the comparison-data.
For target-data or comparison-data, you must specify at least one value expression other than the single dynamic parameter that is specified.
For return-value or predefined-return-value, you must specify at least one value expression other than the following:
- The specification of only a single dynamic parameter
- NULL
If you specify only a single dynamic parameter for target-data, the data type of the dynamic parameter of target-data is assumed to be the data type of the first comparison-data item. Note, however, that if you specify only a single dynamic parameter for the first comparison-data item, the data type of the second or subsequent comparison-data item (which is not an item for which only a single dynamic parameter is specified) is assumed.
If only a single dymanic parameter is specified for comparison-data, the data type of target-data is assumed as the data type of the dynamic parameter. Note, however, that if you specify only a single dynamic parameter for target-data, the data type of the first comparison-data item is assumed. In addition, if you specify only a single dynamic parameter for the first comparison-data item, the data type of the second or subsequent comparison-data item (which is not an item for which only a single dynamic parameter is specified) is assumed.

The data types to be assumed are described based on the following specification examples:
- Specification example 1
```
LTDECODE(?, 10, 'A', 20, 'C')
```
  Because the first comparison-data item (10) is of the INTEGER type, the data type of the dynamic parameter of target-data is assumed to be INTEGER.
- Specification example 2
```
LTDECODE(CURRENT_DATE, ?, 'A', DATE'2017/01/01', 'C')
```
  Because target-data (CURRENT_DATE) is of the DATE type, the data type of the dynamic parameter of comparison-data is assumed to be DATE.
- Specification example 3
```
LTDECODE(?, ?, 'A', 'B', 'C')
```
  For the first comparison-data item, only a single dynamic parameter is specified. The data type of the second comparison-data item ('B') is CHAR(1). For this reason, the data type of the dynamic parameter of target-data and the data type of the dynamic parameter of the first comparison-data item are both assumed to be CHAR(1).
If you specify only a single dynamic parameter for return-value or predefined-return-value, the data type of the dynamic parameter is assumed to be the data type of the scalar function's execution result.

The data types to be assumed are described based on the following specification examples:
- Specification example 1
```
LTDECODE("C1", 10, -1, 20, 0, 30, ?)
```
  Because the data type of the execution result of LTDECODE is INTEGER, the data type of the dynamic parameter of return-value is assumed to be INTEGER.
- Specification example 2
```
LTDECODE("C1", DATE'2017/01/01', 'A', DATE'2017/02/01',
         ?, DATE'2017/02/01', 'BB')
```
  Because the data type of the execution result of LTDECODE is VARCHAR(2), the data type of the dynamic parameter of return-value is assumed to be VARCHAR(2).
- Specification example 3
```
LTDECODE("C1", 10, 'A', 20, ?, ?)
```
  Because the data type of the execution result of LTDECODE is VARCHAR(1), the data types of the dynamic parameters of return-value and predefined-return-value are both assumed to be VARCHAR(1).
The return value that corresponds to the comparison data item whose value is greater than the value in the target data (the following comparison predicate is true) is returned.
```
Target data < Comparison data
```
If there are multiple comparison data items whose values are greater than the value in the target data, the return value that corresponds to the first comparison data item is returned.

To Page Top

(4) Examples

Example 1:

Convert the values in column C1 of table T1 as follows. Then, store the converted values in column C2.

Value less than 0 → Null value
Value greater than or equal to 1 → 2

SELECT "C1", LTDECODE("C1", 0, NULL, 1, "C1", 2) "C2"
    FROM "T1"

[Figure]

Example 2:

Convert the values in the height column (HEIGHT) of the employee table (EMPLIST) as follows. Then, store the converted values in column HEIGHT2.

Value less than 150 → 150
Value greater than or equal to 190 → 190

SELECT "USERID", LTDECODE("HEIGHT", 150, 150, 190, "HEIGHT", 190) "HEIGHT2"
    FROM "EMPLIST"

[Figure]

Example 3:

Search the employee table (EMPLIST), and determine the following values:

Based on the ages (AGE) of the employees, determine the number of employees in each age group.

SELECT "GEN", COUNT("GEN") "GEN-NUM"
   FROM "EMPLIST"
     GROUP BY LTDECODE("AGE", 20, 'Under  20'
                            , 30, '20s'
                            , 40, '30s', '40 and older')
   "GEN"

[Figure]

To Page Top