CODEWITHSUNDEEP
oracle-databaseplsql
challengeAccepted
challengeAccepted

Reputation: 7600

CASE WHEN in WHERE clause is failing with an error missing keyword

In oracle, I am trying to use date as a condition in where clause. What I am looking for is to get employees with if they were created after 7th May 2020, dont use the condition XML_VAL NOT LIKE '%%' but if they were created before then, no extra condition. I am trying to implement like below, but its throwing missing keyword near not like in the case statement. I am not sure what it is I am missing.

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

Upvotes: 0

Views: 86

Answers (1)

MT0
MT0

Reputation: 167981

You cannot use a CASE expression like that as the THEN clause expects a single expression and not a comparison. Instead you want:

SELECT DISTINCT
       LOGINID
FROM   EMPLOYEES
WHERE  EmployeeCode NOT IN ('L35', 'L36')
AND    (  CREATE_DT >= DATE '2020-05-07'
       OR XML_VAL NOT LIKE '%<Product>%' )

I am not sure what it is I am missing.

If you really want to use a CASE expression then this would be syntactically valid:

CASE
WHEN CREATE_DT < TO_DATE('07-MAY-20', 'DD-MON-RR', 'NLS_DATE_LANGUAGE=American')
THEN XML_VAL
ELSE NULL
END NOT LIKE '%<Product>%'

Upvotes: 1

Related Questions