Oracle DECODE Function

The Oracle DECODE function provides similar functionality to an IF-THEN-ELSE statement. The general format for an Oracle DECODE function is:

DECODE(expression, value1, result1 [, valuen, resultn], [default])

In the above example, expression represents what you want to evaluate. The valueX fields represent what you would like expression evaluated against, while the resultX fields are the values you want substituted if a match is found.

Anything you can do with an Oracle DECODE function, you could just as easily achieve with an IF-THEN-ELSE block. You would normally use a DECODE function over the IF-THEN-ELSE when you have a simple comparison or you prefer the readability of a DECODE function.

As a practical example, consider the small block of data below which might represent a subset of data from a multi-user system.

userid username power
10 john 1
11 cameron 2
12 al 5
13 simon 4
14 lucas 0
15 sally 3

If you issued the following SQL statement against that data, you should expect the output below:

SELECT userid, username, DECODE(power, 1, 'Registered', 2, 'Trusted', 3, 'Moderator', 4, 'Author', 5, 'Administrator', 'Pleb') As status FROM Users;

userid username status
10 john Registered
11 cameron Trusted
12 al Administrator
13 simon Author
14 lucas Pleb
15 sally Moderator

The caveat of using the Oracle DECODE function is that the expression must evaluate to a single value. As such, you can not use an evaluation which has multiple possible values. In such a circumstance, you would need to find a way (read: algorithm) to make each possible value evaluate to a single value. By doing so however, you are removing the simplicity of the DECODE function and an IF-THEN-ELSE block would probably suit better.

Next time you’re going to reach for the trusty IF-THEN-ELSE block by default, consider using a DECODE function – in the right circumstances, it’ll make your SQL simpler.

3 thoughts on “Oracle DECODE Function

  1. Dont forget that in some circumstances this kind of construct, be it the IF-THEN-ELSE or the DECODE can be a costly thing to implement.

Comments are closed.