How do you test inequality with Oracle Case Statement

Question

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?

Answer 1

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);

Answer 2

Yes, use CASE WHEN:

CASE 
    WHEN some_formula > 200 THEN 'High'
    WHEN some_formula < 100 THEN 'Low'
    ELSE 'Medium'
END