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?

score 9 · Accepted Answer · answered May 27 '14 at 17:05

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

score 4 · Answer 2 · answered May 27 '14 at 17:03

4

Yes, use CASE WHEN:

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

answered May 27 '14 at 17:03

Lamak

69,480
12
108
116

score 2 · Answer 3 · answered May 27 '14 at 17:03

You can put complicated expressions in the WHEN clause, or you can even call a function if you need to. You can also do complicated expressionsin your THEN clause if you need to. So something like this is easily possible:

SELECT CASE
         WHEN a + b > 200 THEN
           a + b + c 
         WHEN a + b <= 200 THEN 
           a + b + d
       END

How do you test inequality with Oracle Case Statement

3 Answers3