The decode function has the functionality of an IF-THEN-ELSE statement.

The syntax for the decode function is:

decode ( expression , search , result [, search , result]… [, default] )

expression is the value to compare.

search is the value that is compared against expression .

result is the value returned, if expression is equal to search .

default is optional.  If no matches are found, the decode will return default .  If default is omitted, then the decode statement will return null (if no matches are found).

For Example:

You could use the decode function in an SQL statement as follows:

SELECT supplier_name,
decode (supplier_id, 10000, ‘Sausage’, 10001, ‘Eggs’, 10002, ‘Bacon’, ‘Food’) result FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
     result := ‘Sausage’;

ELSIF supplier_id = 10001 THEN
    result := ‘Eggs’;

ELSIF supplier_id = 10002 THEN
    result := ‘Bacon’;

    result := ‘Food’;


The decode function will compare each supplier_id value, one by one.

Decode – Oracle SQL Function
Tagged on: