How do you test inequality with Oracle Case Statement

case-statement, oracle, sql

Solution

`case` supports a syntax to evaluate boolean conditions. It's not as clean as you'd like as you need to re-write each expression, but it gets the job done:

select 
  case
    when (1+2) > 200 then 'high'
    when (1+2) < 100 then 'low'
    else                  'medium'
  end hi_med_low
from dual ;

One possible mitigation could be to use a subquery for the formula, so you only have to write it once:

select 
  case
    when formula > 200 then 'high'
    when formula < 100 then 'low'
    else                    'medium'
  end hi_med_low
from (select (1+2) AS formula from dual);

Problem

This works fine: ``` select case (1+2) -- (or_some_more_complicated_formula_yielding_a_numeric_result) when 200 then '200' when 100 then '100' else 'other' end hi_med_low from dual ; ``` But I need to do something more like this: ``` select case (1+2) -- (or_some_more_complicated_formula_yielding_a_numeric_result) when greater than 200 then 'high' when less than 100 then 'low' else 'medium' end hi_med_low from dual ; ``` Suggestions?

Original source