You can also learn some great SQL tricks at SQL Tricks and Workarounds. If you like this blog, do share with your friends and colleagues on your social media.įor more updates join my facebook group and do like my facebook page. If you have any questions/suggestions, please comment below or write me an email. In our case, ‘NO COUNTRY’ is available has index number 8 and it does not have a pair argument so for all other records which are not in our test case, Oracle has set ‘NO COUNTRY’ in COUNTRY_NAME column.After that every argument which has odd index number consider as default argument if it does not have it’s pair argument. First 3 arguments are compulsory to complete a case.If any argument is found alone (without it’s pair), Oracle considers it as defaul.It means these arguments are found in bound relation. After that search and result arguments are inter-dependent.First argument is always treated as expr.While using DECODE, you must remember a few points. Looking at the syntax and the SQL code might confuse you. We can see in the above table that Country_Name column is showing Countries as per the Country_IDs which we evaluated in our SQL code. When we run above SQL statement, Oracle will generate below output. Let’s understand the concept with an example. You can also use CASE Expressions to achieve the same output. The maximum number of arguments in the DECODE function, including expr, searches, results, and default, are 255. If it is omitted, then Oracle returns null. If expr is not equal to search then Oracle returns default. If expr and search are equal then Oracle returns corresponding results. User provides an argument as a string or expression which needs to be searched in expr. If it is a char string, it must be enclosed in single quoration marks. This includes the expression, search, and result arguments.It can be a column name from a table or a string. Question: Is there a limit to the number of arguments that you can have in one DECODE statement? I'm getting an error, "ORA-00939: too many arguments for function".Īnswer: Yes, the maximum number of components that you can have in a DECODE function is 255. If the first result is NULL, then the return value is converted. If yrs_of_service = 1 and 5 then return 0.06Īnswer: You will need to create a formula that will evaluate to a single number for each one of your ranges.ĭECODE(TRUNC (( yrs_of_service + 3) / 4), 0, 0.04, The DECODE function returns a value that is the same datatype as the first result in the list. Question: I need to write a DECODE statement that will return the following: The formula will evaluate to 2, if the supplier_id is between 21 and 30. The formula will evaluate to 1, if the supplier_id is between 11 and 20. The formula will evaluate to 0, if the supplier_id is between 1 and 10. In this example, based on the formula: TRUNC ((supplier_id - 1) / 10 However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on.ĭECODE(TRUNC ((supplier_id - 1) / 10), 0, 'category 1', Question: I would like to know if it's possible to use the DECODE function for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number.Īnswer: Unfortunately, you can not use the DECODE function for ranges of numbers. The date example above could be modified as follows: LEAST(date1, date2) Helpful Tip #2: One of our viewers suggested using the LEAST function (instead of the DECODE function) as follows: ![]() Sales Bonuses DECODE(SIGN(actual-target), -1, 'NO Bonus for you', 0,'Just made it', 1, 'Congrats, you are a winner') ![]() The SIGN/DECODE combination is also helpful for numeric comparisons e.g. The date example above could be modified as follows: DECODE(SIGN(date1-date2), 1, date2, date1) Helpful Tip #1: One of our viewers suggested combining the SIGN function with the DECODE function as follows: The formula below would equal 0, if date1 is greater than date2: (date1 - date2) - ABS(date1 - date2) Otherwise, the DECODE function should return date1.Īnswer: To accomplish this, use the DECODE function as follows: DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1) Question: One of our viewers wanted to know how to use the DECODE function to compare two dates (ie: date1 and date2), where if date1 > date2, the DECODE function should return date2.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |