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.

Evaluating compound conditions with DECODEĪ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.Using DECODE for Greater than or Less than Comparisons.

The Basics: What it is, and How it works.

Oracle decode