DECODE With Ranges

Tim Hall wrote this post yesterday (as part of Joel Kallman Day 2022) with some examples for “the old way” vs. “the new way” to do some stuff.
One of these examples is DECODE and CASE Expressions. As Tim wrote:

CASE expressions were first released in Oracle 8i […] Prior to that if we wanted something similar to a CASE expression in our SQL, we had to use the DECODE function.

This reminded me of a trick I came up with many years ago, probably before 8i 🙂

A table T has a column X, and we need a query that one of the items it returns is a rank – either “Low”, “Medium” or “High”. This rank is determined by the value of X and two thresholds – LOW_TH and HIGH_TH.

With a (Searched) CASE Expression it’s as simple as this:

select x,
       case
           when x < :low_th then
            'Low'
           when x < :high_th then
            'Medium'
           else
            'High'
       end
from   t;

But how could we do it without CASE?

One option is to write a PL/SQL function that gets X as the input and returns the rank as the output.

But could we have a pure SQL solution? DECODE doesn't look like a good fit, because it works with discrete values, not with ranges.

It is possible. We just need to convert each range to some unique discrete value. As we have 3 ranges, we need 3 values, and there is a SQL function that returns exactly 3 possible values (and was already supported back then) - the sign function.

In order to use the sign function, we first need to map the "Low" range to negative numbers, the "Medium" range to 0, and the "High" range to positive numbers.

This mapping can be done with this expression:

floor((x - :low_th) / (:high_th - :low_th))

And now it's easy to write the final query:

select x,
       decode(
         sign(floor((x - :low_th) / (:high_th - :low_th))),
           -1, 'Low',
            0, 'Medium',
            1, 'High')
from   t;

Obviously it's better having "modern" capabilities such as the CASE expression, but not having them had the advantage of forcing us to be creative 🙂