# Oracle/PLSQL: DECODE Function

Learn how to use the Oracle/PLSQL **DECODE function** with syntax and examples.

## Description

The Oracle/PLSQL **DECODE function** has the functionality of an IF-THEN-ELSE statement.

## Syntax

The syntax for the Oracle/PLSQL **DECODE function** is:

DECODE( expression , search , result [, search , result]... [, default] )

### Parameters or Arguments

*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 function will return *default*. If *default* is omitted, then the DECODE function will return null (if no matches are found).

## Applies To

The DECODE function can be used in the following versions of Oracle/PLSQL:

- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

## Example

The DECODE function can be used in Oracle/PLSQL.

You could use the DECODE function in a 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.

## Frequently Asked Questions

Question: One of our viewers wanted to know how to use the DECODE function to compare two dates (ie: date1 and date2), where if date1 > date2, the DECODE function should return date2. Otherwise, the DECODE function should return date1.

Answer: To accomplish this, use the DECODE function as follows:

DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1)

The formula below would equal 0, if date1 is greater than date2:

(date1 - date2) - ABS(date1 - date2)

**Helpful Tip #1**: One of our viewers suggested combining the SIGN function with the DECODE function as follows:

The date example above could be modified as follows:

DECODE(SIGN(date1-date2), 1, date2, date1)

The SIGN/DECODE combination is also helpful for numeric comparisons e.g. Sales Bonuses

DECODE(SIGN(actual-target), -1, 'NO Bonus for you', 0,'Just made it', 1, 'Congrats, you are a winner')

**Helpful Tip #2**: One of our viewers suggested using the LEAST function (instead of the DECODE function) as follows:

The date example above could be modified as follows:

LEAST(date1, date2)

Question: I would like to know if it's possible to use the DECODE function for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number.

Answer: Unfortunately, you can not use the DECODE function for ranges of numbers. However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on.

For example:

SELECT supplier_id, DECODE(TRUNC ((supplier_id - 1) / 10), 0, 'category 1', 1, 'category 2', 2, 'category 3', 'unknown') result FROM suppliers;

In this example, based on the formula:

TRUNC ((supplier_id - 1) / 10

The formula will evaluate to 0, if the supplier_id is between 1 and 10.

The formula will evaluate to 1, if the supplier_id is between 11 and 20.

The formula will evaluate to 2, if the supplier_id is between 21 and 30.

and so on...

Question: I need to write a DECODE statement that will return the following:

If yrs_of_service < 1 then return 0.04 If yrs_of_service >= 1 and < 5 then return 0.04 If yrs_of_service > 5 then return 0.06

How can I do this?

Answer: You will need to create a formula that will evaluate to a single number for each one of your ranges.

For example:

SELECT emp_name, DECODE(TRUNC (( yrs_of_service + 3) / 4), 0, 0.04, 1, 0.04, 0.06) as perc_value FROM employees;

Question: Is there a limit to the number of arguments that you can have in one DECODE statement? I'm getting an error, "ORA-00939: too many arguments for function".

Answer: Yes, the maximum number of components that you can have in a DECODE function is 255. This includes the *expression*, *search*, and *result* arguments.