大小写表达式问题

Question

我试着在下面运行这个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

结果是

ORA-00905:缺少关键字00905. 00000 -"缺少关键字";*原因:
*行动:Line: 9 Column: 47

你能告诉我如何修复这个错误吗?

Answer 1

!= NULL永远不会为真，CASE表达式没有END。

要修复它，您可以使用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 )

如果必须使用CASE表达式，则THEN/ELSE子句中的表达式必须是一个值(而不是比较)，因此需要将比较放在CASE表达式之外:

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

Answer 2

你能解释一下你在where子句中要做什么吗?

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

当客户名称为'Sai'时，您希望城市显示null吗?如果是-

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 is not null