Case expression Issue-CodePudding

I am trying to run this below select query

select c.customer_id ,
c.customer_name,
c.city

from
customers c
where 
case when c.customer_name = 'Sai' then c.city is null
and c.city!=null

Which is resulting in

ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause:
*Action: Error at Line: 9 Column: 47

Can you please let me know how to fix this error ?

CodePudding user response：

!= NULL will never be true and the CASE expression does not have an END.

To fix it , you can just use AND and OR:

select c.customer_id ,
       c.customer_name,
       c.city
from   customers c
where  ( c.customer_name = 'Sai' AND c.city IS NULL )
OR     ( ( c.customer_name != 'Sai' OR c.customer_name IS NULL )
       AND c.city IS NOT NULL )

If you must use a CASE expression then the expression in the THEN/ELSE clauses must be a value (and not a comparison) so you need to put the comparison outside of the CASE expression:

select c.customer_id ,
       c.customer_name,
       c.city
from   customers c
WHERE  CASE
       WHEN c.customer_name = 'Sai' AND c.city IS NULL
       THEN 1
       WHEN ( c.customer_name != 'Sai' OR c.customer_name IS NULL )
            AND c.city IS NOT NULL
       THEN 1
       END = 1

CodePudding user response：

Can you explain what you're trying to do in your where clause?

where case when c.customer_name = 'Sai' then c.city is null and c.city!=null

Do you want the city to show null when the customer name is 'Sai'? If so -

select c.customer_id ,
c.customer_name,
case when c.customer_name = 'Sai' then null else c.city end as city
from customers c
where c.city!=null