Oracle DECODE function

Preamble

Oracle/PLSQL DECODE function has functionality of IF-THEN-ELSE operator.

Oracle/PLSQL syntax of the DECODE function

DECODE( expression_id , search_id , result_id [, search , result]... [, default] )

Parameters or arguments

• expression_id – is an expression for comparison.
• search_id – value that is compared to.
• result_id – value returned if the expression coincided with the sought search.

default – optional. If no matches are found, the DECODE function will return the default value. If no match is found, DECODE will return NULL (if no match is found).

DECODE in the following versions of Oracle/PLSQL

You can use the DECODE function in an SQL query as follows:

SELECT suppl_name,
DECODE(suppl_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppls;

Request using DECODE is equivalent to the IF-THEN-ELSE design.

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

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

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

ELSE
result := 'Gateway';

END IF;

The DECODE function will compare each suppl_id value, one after the other.

Frequently Asked Questions

Question:

One of our readers wanted to know how to use the DECODE function to compare two dates (that is: date1 and date2), where date1 > date2, the DECODE function should return date2. Otherwise, the DECODE function should return date1.

Answer:

To do so, use the DECODE function as follows:

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

The formula below is 0 if date1 is greater than date2:

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

Useful advice No. 1:
One of our readers suggested combining the SIGN function with the DECODE function as follows:

The example with the dates above can be modified as follows:

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

The combination of SIGN / DECODE is also useful for digital comparisons such as bonus sales.

DECODE(SIGN(actual-target), -1, ‘No bonuses for you’, 0, ‘Just do it’, 1, ‘Congratulations, you are the winner’)

Useful advice No. 2:
One of our readers suggested using the LEAST function (instead of DECODE) as follows:

An example with dates above can be modified as follows:

LEAST(date1, date2)

Question:

I would like to know if it is possible to use the DECODE function for number ranges, i.e. 1-10 = ‘category 1’, 11-20 = ‘category 2’, instead of decoding each number individually.

The answer is:

Unfortunately, you cannot use the DECODE function for number ranges. However, you can try to create a formula that will define one number for one range and another number for another range, and so on.

For example:

SELECT suppl_id,
DECODE(TRUNC ((suppl_id - 1) / 10), 0, 'cat 1',
1, 'cat 2',
2, 'cat 3',
'unknown' result
FROM suppls;

This example, based on a formula:

• TRUNC((suppl_id – 1) / 10)
• The formula will score 0 if suppl_id is between 1 and 10.
• The formula will be evaluated as 1 if suppl_id is between 11 and 20.
• The formula will be evaluated at 2 if suppl_id is between 21 and 30.

Question:

I need to write a DECODE request 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 that?

The answer:

You will need to create a formula that calculates a unit number for each 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 empls;

Question:

Is there a limit on the number of arguments that you can specify in one DECODE operator? I get the error message “ORA-00939: too many arguments for the function”.

The answer is:

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

SQL tutorial: DECODE function in Oracle Database

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.