Decode Function

In Oracle/PLSQL, 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).

 

Applies To:

• Oracle 9i, Oracle 10g, Oracle 11g

 

For example:

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

SELECT supplier_name,
decode(supplier_id,	10000,	'IBM',
	10001,	'Microsoft',
	10002,	'Hewlett Packard',
		'Gateway') result
FROM suppliers;

 

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

IF supplier_id = 10000 THEN
     result := 'IBM';

ELSIF supplier_id = 10001 THEN
    result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
    result := 'Hewlett Packard';

ELSE
    result := 'Gateway';

END IF;

 

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