SQL Case 语句抛出缺少关键字错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16067331/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Case statement throwing missing keyword error
提问by roshanK
I'm trying this query:
我正在尝试这个查询：
Select * from users_t t
where
case when sysdate <= to_date('20130131', 'yyyymmdd')
then t.user_id=1254664
else t.user_id=1259753
End
Why is it giving out "ORA-00905: missing keyword" error?
为什么会发出“ORA-00905：缺少关键字”错误？
回答by Gordon Linoff
You need a comparison operator outsidethe casestatement:
您需要在语句外使用比较运算符case：
Select * from users_t t
where
(case when sysdate <= to_date('20130131', 'yyyymmdd')
then 254664
else 1259753
End) = t.user_id
However, you can write this without the casestatement:
但是，您可以在没有case语句的情况下编写：
select *
from users_t t
where ((sysdate <= to_date('20130131', 'yyyymmdd') and t.user_id = 254664) or
((sysdate > to_date('20130131', 'yyyymmdd') and t.user_id = 1259753)
回答by gustavodidomenico
Your case statement is not correct.
您的案例陈述不正确。
SELECT *
FROM users_t t
WHERE t.user_id = CASE WHEN SYSDATE <= TO_DATE('20130131', 'yyyymmdd')
THEN 1254664
ELSE 1259753
END
This will accomplish your task.
这将完成您的任务。
Edit: Better formatting.
编辑：更好的格式。
回答by Rachcha
A CASEstatement in SQL always returns a value. You need to equate this CASEstatement to something. Read more about it here.
CASESQL 中的语句总是返回一个值。你需要把这个CASE陈述等同于某事。在此处阅读更多相关信息。
You should use your code as following:
你应该使用你的代码如下：
Select * from users_t t
where
t.user_id = case
when sysdate <= to_date('20130131', 'yyyymmdd')
then 1254664
else 1259753
End
回答by Jon Heller
The root cause of this error is that there is no Boolean data type in Oracle SQL. (Which I personally think is a huge mistake.)
此错误的根本原因是 Oracle SQL 中没有布尔数据类型。（我个人认为这是一个巨大的错误。）
Returning a condition is a perfectly valid idea, and it even works in PL/SQL:
返回一个条件是一个完全有效的想法，它甚至适用于 PL/SQL：
declare
v_test Boolean;
begin
v_test := case when 1=1 then 1=1 else 1=2 end;
end;
/
