how to use a constant date and use CASE WHEN in plsql

Question

I want to use a constant date in the where clause and use case statement because I will be calling this query from java service.

SELECT DISTINCT LOGINID
FROM EMPLOYEES
WHERE EmployeeCode NOT IN ('L35', 'L36') and
    CASE WHEN (CREATE_DT > '07-MAY-20') THEN CREATE_DT > ?
    ELSE WHEN (CREATE_DT <= '07-MAY-20') THEN CREATE_DT < ? and XML_VAL NOT LIKE '%<Product>%' END

java service uses the above query and sends a date into it. If CREATE_DT is before or after that 07-MAY-20, the case statement should be used. My question is if I did this right?

Answer 1

Well, your query is invalid so it won't work because of

• invalid syntax and
• the fact that you're most probably comparing date value (create_dt column) with a string.
  • '07-MAY-20' is a string, although it looks like a date and Oracle will try to implicitly convert it into a valid date value. Maybe it'll succeed, maybe not (it would fail in my database, for example). You should compare date to another date, e.g. a date literal as my example shows

This is how I understood the problem.

select distinct loginid
from employees
where employeecode not in ('L35', 'L36') 
  and (
        (    create_dt > ?
         and create_dt >  date '2020-05-07'
        )
       or
       (     create_dt < ?
        and create_dt <= date '2020-05-07'
        and xml_val not like '%<Product>%'        
       )
      );

Answer 2

You cannot use CASE statement as expression itself, It must be part of the expression. So you can update you query to -

SELECT DISTINCT LOGINID
FROM EMPLOYEES
WHERE EmployeeCode NOT IN ('L35', 'L36')
  AND CASE WHEN (CREATE_DT > '07-MAY-20') 
                THEN CASE WHEN CREATE_DT > ? THEN 1 ELSE 0 END
           ELSE CASE WHEN (CREATE_DT < ?) and XML_VAL NOT LIKE '%<Product>%' 
                THEN 1 ELSE 0 END
      END = 1;

Though I have not tried it yet, But this should work.