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.
Syntax:
decode( expression , compare_value, return_value, [,compare, return_value] ... [,default_return_value] )
with:
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.
|
