oracle 在 where 子句中使用 case
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24249323/
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
Using case inside where clause
提问by Rodrigo Souza
I′m trying to create a procedure that has a parameter called m_reaplicacao. This parameter receives the values 'S' for Yes, 'N' for No and 'T' for all records.
When the parameter is Yes, I should return the records with value equals to 9.
When the parameter is No, I should return the records different of 9. And finally, when the the value is All, I should return all records from the table.
With the code bellow, Oracle says:
Compilation errors for PROCEDURE MYDB.CONTAS_A_PAGAR_SPS
Error: PL/SQL: ORA-00905: missing keyword
Line: 84
Text: ta.id_1a_cbr = 9;
我正在尝试创建一个具有名为 m_reaplicacao 的参数的过程。此参数接收值“S”代表是，“N”代表否，“T”代表所有记录。当参数为Yes时，返回值为9的记录。当参数为No时，返回与9不同的记录。最后，当值为All时，返回表中的所有记录. 使用下面的代码，Oracle 说： PROCEDURE MYDB.CONTAS_A_PAGAR_SPS 的编译错误错误：PL/SQL：ORA-00905：缺少关键字行：84 文本：ta.id_1a_cbr = 9；
select * from proposta ta
where
ta.estado = 'RJ'
and case
when m_reaplicacao = 'S' then
ta.id_1a_cbr = 9;
when m_reaplicacao = 'N' then
ta.id_1a_cbr <> 9
else null
end case;
I saw a lot of posts, but I did not solve this one. Can someone help me, please?
我看了很多帖子，但是我没有解决这个问题。有人能帮助我吗？
回答by Justin Cave
Don't use a CASEstatement in a WHEREclause when you really want a simple combination of boolean evaluations.
当您确实需要布尔计算的简单组合时，不要CASE在WHERE子句中使用语句。
WHERE ta.estado = 'RJ'
AND ( m_reaplicacao = 'T'
OR (m_reaplicacao = 'S' AND ta.id_1a_cbr = 9)
OR (m_reaplicacao = 'N' AND ta.id_1a_cbr <> 9)
)
If for some reason you really do want to use a CASEstatement, you'd need the CASEto return a value that you check in the WHEREclause. For example
如果出于某种原因您确实想使用CASE语句，则需要CASE返回您在WHERE子句中检查的值。例如
WHERE ta.estado = 'RJ'
AND (CASE WHEN m_reaplicacao = 'S' AND ta.id_1a_cbr = 9
THEN 1
WHEN m_reaplicacao = 'N' AND ta.id_1a_cbr <> 9
THEN 1
WHEN m_reaplicacao = 'T'
THEN 1
ELSE 2
END) = 1
This is not generally the clearest way to express this sort of condition, however.
然而，这通常不是表达此类条件的最清晰方式。
回答by Hart CO
You cannot return expressions in CASEstatements, easiest to add additional WHEREcriteria sets:
您不能在CASE语句中返回表达式，最容易添加其他WHERE条件集：
select *
from proposta ta
where ta.estado = 'RJ'
and (
(m_reaplicacao = 'S' AND ta.id_1a_cbr = 9)
OR (m_reaplicacao = 'N' AND ta.id_1a_cbr <> 9)
)
Not sure what you want to happen in the NULLsituation.
不确定你想在这种NULL情况下发生什么。
回答by tbone
You can use CASE in select statements, like this:
您可以在 select 语句中使用 CASE，如下所示：
with d as (
select 'A' as val from dual
union
select 'B' as val from dual
union
select 'C' as val from dual
)
select *
from d
where
case
when val = 'A' then 1
when val = 'B' then 1
when val = 'Z' then 1
end = 1;
Here we're looking for rows where val in ('A','B','Z'). In this example, its another way of writing:
在这里，我们正在寻找 val 在 ('A','B','Z') 中的行。在这个例子中，它的另一种写法：
select ... where val in ('A','B','Z');
