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
Advertisement
Answer
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;