Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

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. (Refer to 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.

TOCIndex