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.
Subscribe to be informed about new posts, tips and more awesome things.
wpDiscuz