4.9.2.28 DECODE function (extension)

Syntax

DECODE ( expression, search_expression, match_expression
               [ , search_expression, match_expression ...]
               [ , default_expression ] )

Description

The DECODE scalar function is a type of conditional expression. (See the topic on Conditional Expressions for a summary of all the conditional expressions.)

The scalar function DECODE compares the value of the first argument expression with each search_expression and if a match is found, returns the corresponding match_expression. If no match is found, then the function returns default_expression. If default_expression is not specified and no match is found, the function returns a null value.

DECODE provides a subset of the functionality of CASE that is compatible with Oracle SQL syntax. Use a simple case expression for SQL-compatible syntax (see CASE (SQL-92 Compatible)).

Example

SELECT ename, DECODE (deptno,
                         10, 'ACCOUNTS    ',
                         20, 'RESEARCH    ',
                         30, 'SALES       ',
                         40, 'SUPPORT     ',
                         'NOT ASSIGNED'
                    )
     FROM employee ;

Notes

• The first argument expression can be of any type. The types of all search_expressions must be compatible with the type of the first argument.
• The match_expressions can be of any type. The types of all match_expressions must be compatible with the type of the first match_expression.
• The type of the default_expression must be compatible with the type of the first match_expression.
• The type of the result is the same as that of the first match_expression.
• If the first argument expression is null then the value of the default_expression is returned, if it is specified. Otherwise null is returned.