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