how to using BETWEEN in CASE-CodePudding

so I want to make a case where if between the year of acceptance and the current year (sysdate) it is 1 to 5 it will be rank 1 and 6 to 10 rank 2

I using the code like this

select first_name,
       case trunc(months_between(sysdate, hire_date) / 12)
         when between 1 and 5 then
          '1'
         when between 6 and 10 then
          '2'
         when between 11 and 15 then
          '3'
         else
          '4'
       end as information
  from employees;

But error it say 'missing keyword' in the when between 1 and 5

where EMPLOYEES table contains EMPLOYEE_ID,FIRST_NAME,HIRE_DATE columns

CodePudding user response：

Since the expression should individually be written after each when clause such as

select first_name,
       case 
         when trunc(months_between(sysdate, hire_date) / 12) between 1 and 5 then
          '1'
         when trunc(months_between(sysdate, hire_date) / 12) between 6 and 10 then
          '2'
         when trunc(months_between(sysdate, hire_date) / 12) between 11 and 15 then
          '3'
         else
          '4'
       end as information
  from employees;

or more elegant option would be

with emp(first_name,year_diff) as
(
 select first_name, trunc(months_between(sysdate, hire_date) / 12) from employees
)
select first_name,
       case 
         when year_diff between 1 and 5 then
          '1'
         when year_diff between 6 and 10 then
          '2'
         when year_diff between 11 and 15 then
          '3'
         else
          '4'
       end as information
  from emp;