How to find less than by decode?-CodePudding

ORA-00907: missing right parenthesis

00907\. 00000 -  "missing right parenthesis"

SELECT last_name
      ,salary
      ,DECODE(salary, salary <  6000, 0.36,
                      salary <  8000, 0.38,
                      salary < 10000, 0.4,
                      salary < 12000, 0.42,
                      salary < 14000, 0.44,
                      salary < 16000, 0.45) as "TAX RATE"
  FROM employees;

CodePudding user response：

Don't make your life a misery. Use CASE expression instead of DECODE.

select case when salary <  6000 then 0.36
            when salary <  8000 then 0.38
            ...
            when salary < 16000 then 0.45
            else null
       end as tax_rate
from employees

If you'd want to do it with DECODE, you'd use SIGN function (to check whether salary is less than certain amount), and then nest as many DECODEs as necessary. That works, but is a nightmare to maintain.

I don't have your table so I'll try to illustrate it using Scott's emp:

SQL> select ename, sal,
  2    decode(sign(sal - 1000), -1, 'less than 1000',
  3      decode(sign(sal - 1500), -1, 'less than 1500',
  4        decode(sign(sal - 2000), -1, 'less than 2000'))
  5          ) rate
  6  from emp;

ENAME             SAL RATE
---------- ---------- --------------
SMITH             840 less than 1000
ALLEN            1600 less than 2000
WARD             1250 less than 1500
JONES            2975
MARTIN           1250 less than 1500
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
<snip>

If you compare it to CASE, difference is more than obvious:

SQL> select ename, sal,
  2    case when sal < 1000 then 'less than 1000'
  3         when sal < 1500 then 'less than 1500'
  4         when sal < 2000 then 'less than 2000'
  5    end rate
  6  from emp;

ENAME             SAL RATE
---------- ---------- --------------
SMITH             840 less than 1000
ALLEN            1600 less than 2000
WARD             1250 less than 1500
JONES            2975
MARTIN           1250 less than 1500
<snip>