The Oracle Decode Function

At first blush, PL/SQL appears to lack anything remotely like a case statement so that the only way to make complex decisions is by means of ugly chains of if statements. In fact though, Oracle has an extremely powerfull case statement which has the additional advantage of being available to standard queries.

The format of the decode statement is...

        decode(value, test_1, return_1, test_2, return_2, ... test_n, return_n, else)

Value may be any column returned by a query, the result of any operation (such as taking one number from another) or the result of running any function such as substr().

test_1 .. test_n is any value you wish to test value against such as a string or a number

return_1 .. return_n is the result to return if value matches the preceding test.

else is the result to return if value fails to match any of the tests.

Readers who use 'C' or similar languages will recognise this as a much extended version of the C conditional syntax (test_value ? true_result : false_result)

Decode is frequently used in accounts applications to simplify tasks such as aging accounts.  As an example, consider a table containing account balances, then the following code...

select customer,
  decode(trunc((SYSDATE - entrydate)/30),0,balance, NULL) as current
  decode(trunc((SYSDATE - entrydate)/30),1,balance, NULL) as _30
  decode(trunc((SYSDATE - entrydate)/30),2,balance, NULL) as _60
  decode(trunc((SYSDATE - entrydate)/30),3,balance, NULL) as _90

might produce a report looking something like this...

customer        current        _30        _60        _90
------------  ---------  ---------  ---------  ---------
ASG              233.89
ASG                         342.12
ASG                                    103.87
BMG               26.90
BMG                                               456.23
CFR                         107.98

...and so on.

Note the use of trunc in the above query. Applied to a number without any argument, trunc returns an integer result so that, for example, trunc(43 / 30) will return 1 and trunc(78/30) will return 2.