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 the default_expression. If a default_expression is not specified and no match is found, then the function returns a NULL value.

Syntax

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

Example

This example illustrates one way to use the DECODE function:

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

Notes

Use a simple case expression when SQL‑compatible syntax is a requirement.

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.

Compatibility

Progress extension