Oracle DECODE Function

• Tweet

• Tweet

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.

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;

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.

Related posts:

1. Oracle Dynamic SQL Using The DECODE Function
2. Oracle COALESCE Function
3. Oracle RETURNING Clause
4. Oracle Blogs
5. Oracle Express (XE)