Need help in converting a case expression to decode in oracle-CodePudding

I have this case expression in oracle and I want to convert it into decode but I am unable to, can someone help me out?

CASE 
 WHEN DAYS <= 0 THEN 'Current'
 WHEN (DAYS > 0 and  DAYS < 31) then '01 - 30 Days' 
 WHEN  (DAYS > 30 and  DAYS < 61) then '31 - 60 Days'
 WHEN  (DAYS > 60 and  DAYS < 91) then '61 - 90 Days' 
 ELSE '91  Days'
END as Days

when trying to do decode

error: missing right parenthesis

I want to know, is there a way to add a condition in decode?

CodePudding user response：

Oracle's DECODE() function only works when your CASE expression has equality checks, but not for inequalities. However, you may shorten your logic a bit:

CASE WHEN DAYS <= 0 THEN 'Current'
     WHEN DAYS < 31 THEN '01 - 30 Days'
     WHEN DAYS < 61 THEN '31 - 60 Days'
     WHEN DAYS < 91 THEN '61 - 90 Days'
     ELSE '91  Days' END AS Days

Note that I have dropped the lower range check. It is possible to do this because for each condition, the previous range in the CASE expression rules out the possibility of a lower match.

CodePudding user response：

with Day as
 (select CASE
           WHEN DAYS <= 0 THEN 1
           WHEN DAYS < 31 THEN 2
           WHEN DAYS < 61 THEN 3
           WHEN DAYS < 91 THEN 4
           ELSE 5
         END AS Days
    from dual)

SELECT decode(Days,
              1, 'Current',
              2, '01 - 30 Days',
              3, '31 - 60 Days',
              4, '61 - 90 Days',
              5, '91   Days') Days
  FROM day