Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

how to using BETWEEN in CASE

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

where
EMPLOYEES table contains EMPLOYEE_ID,FIRST_NAME,HIRE_DATE columns

>Solution :

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading