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’;
ELSE
result := ‘Food’;
END IF;
The decode function will compare each supplier_id value, one by one.