CASE WHEN versus DECODE

[dr_gori]

Sometimes the case when breaks the branch ... but what to do when can not use it? (as in versions prior to 8i, and in PL * SQL prior to 9i). Simplex!

This is our query:

Select all

SQL> select  
  2    empno 
  3  , sal 
  4  , case  
  5      when sal<1000  then 'SALARIO BAIXO' 
  6      when sal>=1000 then 'SALARIO ALTO' 
  7    end comentario  
  8  from emp 
  9  / 
 
     EMPNO        SAL COMENTARIO 
---------- ---------- ------------- 
      7369        800 SALARIO BAIXO 
      7499       1600 SALARIO ALTO 
      7521       1250 SALARIO ALTO 
      7566       2975 SALARIO ALTO 
      7654       1250 SALARIO ALTO 
      7698       2850 SALARIO ALTO 
      7782       2450 SALARIO ALTO 
      7788       3000 SALARIO ALTO 
      7839       5000 SALARIO ALTO 
      7844       1500 SALARIO ALTO 
      7876       1100 SALARIO ALTO 
      7900        999 SALARIO BAIXO 
      7902       3000 SALARIO ALTO 
      7934       1000 SALARIO ALTO 

And now, a query exactly equal without such a case-when ...

Select all

SQL> select  
  2    empno 
  3  , sal 
  4  , decode(sign(sal-1000), -1, 'SALARIO BAIXO', 'SALARIO ALTO') 
  5  from emp 
  6  / 
 
     EMPNO        SAL DECODE(SIGN(S 
---------- ---------- ------------- 
      7369        800 SALARIO BAIXO 
      7499       1600 SALARIO ALTO 
      7521       1250 SALARIO ALTO 
      7566       2975 SALARIO ALTO 
      7654       1250 SALARIO ALTO 
      7698       2850 SALARIO ALTO 
      7782       2450 SALARIO ALTO 
      7788       3000 SALARIO ALTO 
      7839       5000 SALARIO ALTO 
      7844       1500 SALARIO ALTO 
      7876       1100 SALARIO ALTO 
      7900        999 SALARIO BAIXO 
      7902       3000 SALARIO ALTO 
      7934       1000 SALARIO ALTO 
SQL> 

[dr_gori]

Another way to do this without case-when:

Select all

select 
  empno 
, sal 
, decode(greatest(sal,1000), 1000, 'SALARIO BAIXO', 'SALARIO ALTO') 
from emp 
/ 

[fabissilva]

very practical.
better than using CASE.

[tiago_pimenta]

:-O