Rewriting case when in sql

Question

Hi Im trying to rewrite the following code that uses CASE WHEN. I was thinking that I can instead use decode or something else?

The code:

create table want as select 
case when (Var1<20 ) then 1 
when (40>Var1>=20 ) then 2 
when (Var1>=40 ) then 3 
else .
end as Var1

Why do you want to rewrite it? What is the problem with the current code? — , Feb 07 '18 at 17:02
@a_horse_with_no_name Im learning sql and would like to know different ways to solve problem — Alex T, Feb 07 '18 at 17:05

score 0 · Answer 1 · answered Feb 07 '18 at 17:09

0

This is more simply written as:

create table want as
    select (case when Var1 < 20 then 1 
                 when Var1 < 40 then 2 
                 when Var1 >= 40 then 3 
                 else NULL
            end) as Var1

answered Feb 07 '18 at 17:09

Gordon Linoff

1,242,037
58
646
786

Well this is same code but formatted in a better way, however I was thiknking about using for example decode or some other function in sql. – Alex T Feb 07 '18 at 17:19
3

I'm not sure if decode is officially deprecated, but everyone recommends using case instead. https://stackoverflow.com/questions/3193692/case-vs-decode – kfinity Feb 07 '18 at 18:16

score 0 · Answer 2 · answered Feb 07 '18 at 21:03

decode() only supports equality. One of the reasons why we welcomed case() when it was introduced back in the day was precisely because it allowed us to test greater than and less than.

However it is possible to nest decode() calls and combine them with other functions to achieve the same outcome. This ...

select id
     , var1
     , decode(greatest(var1,20), 20, 1, 
              decode(least(var1,40), 40, 3, 2 )) as trans_var
from tab
/

... implements the logic of your case() statement:

select id
       , var1
       , (case when Var1 < 20 then 1 
                 when Var1 < 40 then 2 
                 when Var1 >= 40 then 3 
                 else NULL
            end) as trans_var
from tab
/

SQL Fiddle demo.

Rewriting case when in sql

2 Answers2