CASE statement in WHERE CALUSE – Missing keyword error

I have list of products which gets renewed on monthly or yearly or both. If the renewal value is ‘M’ then the product is renewed on monthly basis. If the renewal value is ‘Y’ the the product is renewed on yearly basis.

So my query is, If I choose method(M/Y) and a particular date(P_date), I want the details of products that are eligible for renewal.

For eg:

P_no    start_date   renewal_date   end_date     
1001    01-01-2022   01-02-2022     31-01-2022    
1002    01-01-2022   01-01-2023     31-12-2022

If I choose P_date as 06-01-2022 and renew as M then P_no 1001 should be selected

If I choose P_date as 06-01-2022 and renew as A then P_no 1002 should be selected

I wrote the below query

select * from products 
where P_no in('1001','1002') and 
CASE renew
   WHEN renew = 'M' and round(months_between(renewal_date,start_date)) = 1 then
        TO_CHAR(TO_DATE (P_date,'DD-MM-YYYY'),'DD-MON-YYYY') BETWEEN  start_date AND  end_date
    WHEN renew='Y' and round(months_between(renewal_date,start_date)) = 12 then 
        TO_CHAR(TO_DATE (P_date,'DD-MM-YYYY'),'DD-MON-YYYY') BETWEEN start_date AND end_date
    end   ;

and got error:

Missing Keyword

What am I doing wrong?

Answer

You are trying to change the conditions that applied to the query based on the case expression, which won’t work; you could have that evaluate to say 0/1 and compare with than, but it’s just going to make it harder to read.

Instead, just use Boolean logic for the whole thing; assuming renew can only ever be M or Y then maybe:

select * from products 
where P_no in('1001','1002') and 
(
  (
    renew = 'M' and round(months_between(renewal_date,start_date)) = 1 and
        TO_CHAR(TO_DATE (P_date,'DD-MM-YYYY'),'DD-MON-YYYY') BETWEEN  start_date AND  end_date
  )
  or
  (
    renew='Y' and round(months_between(renewal_date,start_date)) = 12 and 
        TO_CHAR(TO_DATE (P_date,'DD-MM-YYYY'),'DD-MON-YYYY') BETWEEN start_date AND end_date
  )
);

or perhaps:

select * from products 
where P_no in('1001','1002') and 
(
  (renew = 'M' and round(months_between(renewal_date,start_date)) = 1)
  or
  (renew='Y' and round(months_between(renewal_date,start_date)) = 12)
)
and TO_CHAR(TO_DATE (P_date,'DD-MM-YYYY'),'DD-MON-YYYY') BETWEEN  start_date AND  end_date;

Although either way your conversion of p_date looks very odd. If that is a date data type (as it should be, if possible) then just do:

P_date BETWEEN  start_date AND end_date

and if it’s a string convert it to a date, but not back to a string again:

TO_DATE (P_date,'DD-MM-YYYY') BETWEEN  start_date AND end_date

Of course, that assumes start_date and end_date are also dates. If they are strings (and they shouldn’t be) then P_date wouldn’t need to be converted, but the comparison wouldn’t work as you expect with the format you’re seeing/using.