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
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.