4

The Powerful DECODE Function

Oracle’s DECODE is one of the functions I find more powerful and one that over the years I have used thousands of times to solve several different kinds of problems. Here I’m going to talk a little bit about how it works, and will show you some interesting ways to use it.

The Basics: What it is, and How it works

Although DECODE is very powerful, how it works is actually very easy to understand: It compares the expression passed in as the first argument, to each of the search values passed in subsequent arguments, one by one, and if it finds a match, returns the corresponding result, otherwise returns the default value.

😐 Words tend to complicate things. Let me explain it this way:

This is the syntax:

DECODE(expression, search1, result1, search2, result2, ..., searchN, resultN, defaultResult)

So, if expression is equal to search1, it returns result1. If expression is equal to searchN, it returns resultN. If no search value from the list is equal to expression, then defaultResult is returned (or null, if no default is provided).

Now, with the basics covered, let’s take a look at some of the interesting ways you could use it.

Using DECODE for Greater than or Less than Comparisons

We can’t use DECODE to perform greater/less than comparisons directly, because by definition, DECODE compares expression to the search expressions provided, and returns some result when they match, that is, when expression is equal to one of the search expressions. DECODE performs equality comparisons, and there is no way to tell it to do some other type of comparison.

However, we can use it in combination with the SIGN() function, to perform greater/less than comparisons. SIGN() will return -1 if the expression passed as argument is less than 0 and will return 1 if it is greater than 0 (if it is equal to 0, it will return, well, 0).

Let’s see the idea with an example. Say we want to apply a 2.5 commission rate for sales that are less than or equal to $10,000.00 and 5.0 for sales greater than $10,000.00

We can get the commission rate using DECODE and SIGN this way:

SELECT amount,
       DECODE(SIGN(amount-10000),
       1,5.0,
       2.5) comission_rate
FROM our_sales_table
WHERE commission_enabled='Y';

You probably don’t need an explanation, but just in case: If amount is greater than $10K then SIGN will return 1 and DECODE will return the result for the first search expression (1) which is 5.0. If amount is less than or equal to $10K then the only search expression we provided doesn’t match, and DECODE returns the default result, which is 2.5.

Using DECODE for Range Comparisons

Now, let’s suppose we have to charge for some service based on how old the customer is, and we have to charge $10.00 if they are younger than 10, $15.00 if they are between 10 and 19, $25.00 if they are between 20 and 29, and $50.00 if they are 30 or older. How would we do that?

It turns out that DECODE is also very useful for range comparisons if we use it in conjunction with some other functions depending on the situation. In this case, we can use it with the TRUNC function, this way:

SELECT
  age,
  DECODE(TRUNC(age/10),
    0,10.00,      -- 0-9
    1,15.00,      -- 10-19
    2,25.00,      -- 20-29
    50.00) charge -- 30+
FROM customers;

Here I’m using the integer part of the division by 10 to determine which range the customer’s age falls into.

Using DECODE for Conditional Counting

I am currently working for a telephony company, so I have to work with CDRs quite often. A CDR is a record that represents a telephone call, in which we register important data about it, such as, phone numbers involved, date and time, duration of the call, etc. and one common requirement is to generate reports that count the number of successful and failed calls over a period of time.

I’m simplifying this a little to use it as an example, but suppose that to determine if a call was connected we need to look at the duration. If it is 0, it means it did not connect, otherwise, the call was connected and our customers were able to talk at least for a second. But if it didn’t connect, we need to look at the release_code to determine if the call failed, or it didn’t connect simply because the receiving person didn’t answer the phone.

So, in this example our requirement is to count the number of connected, unanswered and failed calls per hour for a given date. Let’s see how we can do this in a single query using DECODE and SUM:

SELECT
  TO_CHAR(calldate,'hh'),
  SUM(DECODE(duration,0,0,1)) connected,  -- If duration != 0, sum 1, i.e. count it as connected
  SUM(DECODE(duration,0,DECODE(release_code,16,1,0),0) unanswered,  -- If duration = 0 and release_code = 16 count it as unanswered
  SUM(DECODE(duration,0,DECODE(release_code,16,0,1),0) failed       -- If duration = 0 and release_code != 16 count it as failed
FROM cdr
WHERE
  TRUNC(calldate) = '25-aug-2015'
GROUP BY TO_CHAR(calldate,'hh')
ORDER BY 1;

Here, although I’m really counting records, I’m using the SUM function to do it. As mentioned in the code comments, I’m summing 1 record to the count when the appropriate condition is met. Note also that I’m nesting DECODE calls to evaluate compound conditions as explained in the next section.

Evaluating compound conditions with DECODE

I used ‘compound conditions’ because I didn’t find a better word 😉 , but I’m talking about having several conditions with ANDs and ORs.

The DECODE function evaluates only one condition, and as mentioned earlier, it must be an equality condition, but we can nest several DECODE calls to evaluate more complex ones. Let’s use an example:

Suppose we have a list of products that can or cannot be sold depending on certain conditions. A product is saleable if it has been verified (column verified=’Y’) or if it is not expired and is in stock. How do we get a list of products with a column that tells us if it can be sold?

SELECT
  product_id,
  product_name,
  DECODE(verified,'Y','YES',                  -- If verified=Y it is saleable
    DECODE(SIGN(sysdate-expiration_date),1,   -- If not verified but not expired, we need to check stock
      DECODE(in_stock,'Y','YES','NO'),'NO')) as saleable  -- If not expired and in stock, then is saleable otherwise no
FROM PRODUCTS
ORDER BY product id;

Here I have only 3 levels but you can nest much more levels, as needed to evaluate the desired conditions.

Conclusion

I have only shown here some examples of how you can use this powerful function to do different things. It’s so cool that it even works well with nulls (I mean, DECODE(something,null,…actually performs an ‘is null’ check). Combining it with other functions can help you do ‘like’ comparisons and some other cool things, but it all depends on your creativity.

So, what other not-so-obvious things have you achieved using DECODE? I’d love to hear your experiences.

Final Words: Every thing that can be done with the DECODE function, can also be done with CASE expressions, and they usually make code more readable and understandable, so make sure to check them out too.

Opt In Image
Don't want to miss any Oracle SQL tips?

Subscribe to be informed about new posts, tips and more awesome things.

Carlos

Carlos

I’ve been working with Oracle databases on a daily basis for more than 10 years.

Leave a Reply

4 Comments on "The Powerful DECODE Function"

avatar
Sort by:   newest | oldest | most voted
trackback

[…] standard way to implement IF … THEN … ELSE logic in SQL statements. Oracle provides de DECODE function, which does something similar and is widely used by Oracle developers, but CASE expressions are […]

JH
Guest

I agree this can be done with DECODE. But you need to perform tricks, which makes the code much less understandable than the equivalent and much cleaner CASE expression.
E.g. why use the cryptical
DECODE(SIGN(amount-10000), 1, 5.0, 2.5)
for greater than / less than?

You can simply code it like
CASE when amount > 10000 then 5.0 else 2.5 end
which expresses the same thing in a much cleaner and more understandable way!
And especially compound conditions can be done so much easier with CASE, just with AND and OR instead of inconvenient nested functions.

Please do not teach ignorant programmers these really BAD habit of using DECODE !!

Everything you can think of with DECODE can be done much better with CASE. And, CASE is ANSI SQL, whereas DECODE is proprietary Oracle, and old fashioned too.

Carlos
Admin

Hey JH!

Thanks for commenting.

There’s a pingback here to my article about CASE expressions, which starts with this text: “CASE expressions are the standard way to implement IF … THEN … ELSE logic in SQL statements. Oracle provides the DECODE function, which does something similar and is widely used by Oracle developers, but CASE expressions are actually more flexible and often make statements more readable.”.

I totally agree that CASE expressions make code more readable in situations where the condition is not very simple, but not all of your queries are going to be part of a script or an application that other people will have to maintain. When you are writing code that you will only use once then how to write it becomes totally a matter of preference.

For one-time queries I tend to use DECODE much more than case expressions. I’m so used to it that these kind of “tricks” feel very natural to me, and I write them very quickly, but when I write code that is part of an application I tend to use DECODE only when the condition is simple.

I’m not recommending to always use DECODE, but I also don’t recommend to always use CASE expressions.

Thanks for chiming in.

trackback

[…] standard way to implement IF … THEN … ELSE logic in SQL statements. Oracle provides the DECODE function, which does something similar and is widely used by Oracle developers, but CASE expressions are […]

wpDiscuz