Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
Functions, 33 of 166
decode::=
decode
A 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 CHAR
, VARCHAR2
, NCHAR
, or 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 VARCHAR2
.
In a 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.
See Also:
|
This example decodes the value warehouse_id
. If warehouse_id
is 1, the function returns 'Southlake
'; if warehouse_id
is 2, it returns 'San Francisco
'; etc. If warehouse_id
is not 1, 2, 3, or 4, the function returns 'Non-domestic
'.
SELECT product_id, DECODE (warehouse_id, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non-domestic') quantity_on_hand FROM inventories;
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|