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.
Interesting, I’ve not encountered that one before. I’ll have to keep in mind, could prove useful.
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.
Explanation is good. Example is self explanatory.