ORA-00905:where case语句中缺少关键字

Question

select m_seqe
,m_emai
,m_phon
,e_seqe
,m_id
,
( case 
when (:M_STAT = 0 )  and (m_id is not null ) then 'a'
when (:M_STAT = 0 )  and (m_id is null  ) then 'b'
when (:M_STAT = 200 ) then 'c'
Else ' - ' end ) as Stat
from m_users 
where m_stat = :M_STAT   
and  m_id =
case when (:M_ID = 0 ) then m_id is null
when (:M_ID ='a') then m_id is not null
else 'do nothing' end 

Answer 1

你把它放在&我理解你的意思，这将是几个OR条件的组合：

WHERE   m_stat = :M_STAT
AND (   ( m_id IS NULL     AND :m_id = 0)
OR ( m_id IS NOT NULL AND :m_id = 'a')
OR ( m_id = 'do nothing'
);

Answer 2

不能使用事例表达式来确定过滤器中的逻辑；所以这是没有意义的，也是解析器所抱怨的：

and  m_id =
case when (:M_ID = 0 ) then m_id is null
when (:M_ID ='a') then m_id is not null
else 'do nothing' end 

您可能有一个大小写表达式，它的计算结果是一个值，然后将其与列值进行比较，但这在这里似乎也没有意义，尤其是因为null处理有点尴尬。

你似乎想用简单的布尔逻辑来代替它：

where m_stat = :M_STAT   
and ((:M_ID = 0 and m_id is null)
or (:M_ID ='a' and m_id is not null))

或者在将绑定变量视为字符串时保持一致：

where m_stat = :M_STAT   
and ((:M_ID = '0' and m_id is null)
or (:M_ID ='a' and m_id is not null))

没有什么可担心的；您的'do nothing'字符串似乎是一个占位符，并不打算实际与任何内容匹配(即，它不是您要查找的真正的m_id值(。如果给定行的两个组合条件都不为true，则整体条件为false，并且该行将不匹配，并且将被过滤掉。

Answer 3

您的问题不明确，但可以按以下实现

select m_seqe
,m_emai
,m_phon
,e_seqe
,m_id
,
( case 
when (:M_STAT = 0 )  and (m_id is not null ) then 'a'
when (:M_STAT = 0 )  and (m_id is null  ) then 'b'
when (:M_STAT = 200 ) then 'c'
Else ' - ' end ) as Stat
from m_users 
where m_stat = :M_STAT and 
(
case when when (m_id IS NOT NULL AND :m_id = 'a') then 'a'
when (m_id IS NULL AND :M_ID = 0 ) then 'b'
when (m_id = 'do nothing') then 'c'
when 'd' end 
) in ('a','b','c')