Oracle SQL: the Essential Reference

DECODE

DECODE(expr, search,result [,search,result...] [,default])

Provides the capabilities of an inline IF statement. DECODE is arguably one of the most useful of Oracle's built-in functions. You pass DECODE an input value and a list of value/result pairs. DECODE then looks for the pair in which the value matches the input. When that pair is found, DECODE returns the result from that pair as the result of the function. If no matching value is found, DECODE returns the default result.

DECODE is one of the few functions that can correctly operate on a NULL value. DECODE cannot be invoked from a PL/SQL expression; it can only be invoked from an SQL statement.

Parameters

expr

Specifies an input value. DECODE compares this value with subsequent search values to find the matching value/result pair.

search

Specifies the value portion of a value/result pair.

result

Specifies the result portion of a value/result pair.

default

Specifies an optional default result that DECODE returns if none of the search values match the input expression.

DECODE is limited to a maximum of 255 parameters.

Datatypes are controlled by the first search,result pair. The input expression and all search values are converted to the datatype of the first search value. The return value is converted to the datatype ...