DECODE
The DECODE function compares an expression with a search value and, when true, returns the specified result. If no match is found, DECODE returns the default value, if specified. If the default value is omitted, then DECODE returns NULL.
Syntax
DECODE (expression, search_value, result, [search_value, result]...[,default])
Remarks
• If the expression and search_value are NULL, the result is returned.
• To determine the data type of the output value for DECODE, using the result values, apply the ANSI SQL rules of data type precedence. The search_value has no effect on the output data type.
• DECODE treats empty strings as NULL.
The following table lists the data types of the input arguments for DECODE.
Data Type of expression | Output Type |
BINARY, DATE, DECIMAL, FLOAT, INTEGER, INTERVAL_DAY, INTERVAL_YEAR, NULL, STRING, TIME, TIMESTAMP, XML | Follows the ANSI SQL rules for data type precedence. |
Example
SELECT supplier_name,
DECODE (supplier_id,
10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;
This example is equivalent to:
CAST WHEN supplier_id = 10000 THEN 'IBM'
WHEN = 10001 THEN 'Microsoft'
WHEN = 10002 THEN 'Hewlett Packard'
ELSE 'Gateway'; END