|Oracle9i SQL Reference
Release 1 (9.0.1)
Part Number A90125-01
Functions, 33 of 166
DECODE function compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, returns null.
If expr and search contain character data, Oracle compares them using nonpadded comparison semantics. expr, char1, and char2 can be any of the datatypes
NVARCHAR2. The string returned is of
VARCHAR2 datatype and is in the same character set as expr.
The search, result, and default values can be derived from expressions. Oracle evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle never evaluates a search if a previous search is equal to expr.
Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype
CHAR or if the first result is null, then Oracle converts the return value to the datatype
DECODE function, Oracle considers two nulls to be equivalent. If expr is null, Oracle returns the result of the first search that is also null.
The maximum number of components in the
DECODE function, including expr, searches, results, and default is 255.
This example decodes the value
warehouse_id is 1, the function returns '
warehouse_id is 2, it returns '
San Francisco'; etc. If
warehouse_id is not 1, 2, 3, or 4, the function returns '