Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
  Decode function


Oracle (var)char functions
Instr function
Number format
Kill oracle session
to_date function
Oracle sysdate
Oracle substr
How to use the DECODE statement
How to use the CASE statement
How to use the NVL statement
Using XML functions
Oracle date format
Oracle numeric functions
Oracle date functions
Pl sql trim


The Oracle decode function

The decode function can be used in SQL for and IF-THEN-ELSE construction. It's an alternative for the CASE statement which was introduced in Oracle 8.
decode( expression , compare_value, return_value, [,compare, return_value] ... [,default_return_value] )
expression is the value to evaluate
compare_value is the value that can match the evaluated value
return_value is the value that is returned if compare_value equals the value.
The default_return_value is the value that is returned if no match is found.

To evaluate this expression, Oracle compares the expression to each compare_value one by one. If expression is equal to a compare_value, Oracle returns the corresponding return_value. If no match is found, Oracle returns the defaul_return_value. If no default value is specified, the null value will be returned.
Sample code
select id, decode(status,'A','Accepted','D','Denied','Other')
from   contracts;

Will return for each id:
If status = 'A' : 'Accepted' 
If status = 'D' : 'Denied' 
Else            : 'Other'

Oracle automatically converts the values for expression and compare_value to the datatype of the first compare_value. Also the datatype of the return_value is converted to the datatype of the first return_value. 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.
Note: two null values are considered equivalent in the decode statement.