So, in this example our requirement is to count the number of connected, unanswered and failed calls per hour for a given date. 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. 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. 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. and one common requirement is to generate reports that count the number of successful and failed calls over a period of time. 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. I am currently working for a telephony company, so I have to work with CDRs quite often. Here I’m using the integer part of the division by 10 to determine which range the customer’s age falls into. 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. 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. 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. 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. We can get the commission rate using DECODE and SIGN this way:ĭECODE (SIGN (amount - 10000 ), 1, 5.0, 2.5 ) comission_rate 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 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). However, we can use it in combination with the SIGN() function, to perform greater/less than comparisons. DECODE performs equality comparisons, and there is no way to tell it to do some other type of comparison. 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. Using DECODE for Greater than or Less than Comparisons Now, with the basics covered, let’s take a look at some of the interesting ways you could use it. If no search value from the list is equal to expression, then defaultResult is returned (or null, if no default is provided). If expression is equal to searchN, it returns resultN. So, if expression is equal to search1, it returns result1. This is the syntax: DECODE(expression, search1, result1, search2, result2. The Basics: What it is, and How it worksĪlthough 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. Here I’m going to talk a little bit about how it works, and will show you some interesting ways to use it. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |