where子句中的用例 [英] Using case inside where clause

问题描述

我正在尝试创建一个过程，该过程具有一个称为m_reaplicacao的参数.该参数为是"，"N"为否"和"T"为所有记录接收值. 当参数为是"时，我应返回值等于9的记录. 当参数为No时，我应该返回不同于9的记录.最后，当值为All时，我应该返回表中的所有记录. 使用下面的代码，Oracle说: PROCEDURE MYDB.CONTAS_A_PAGAR_SPS的编译错误 错误:PL/SQL:ORA-00905:缺少关键字 线:84 文字:ta.id_1a_cbr = 9;

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;

    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?

推荐答案

当您确实想要布尔值的简单组合时，请不要在WHERE子句中使用CASE语句.

Don't use a CASE statement in a WHERE clause when you really want a simple combination of boolean evaluations.

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)
      )

如果由于某些原因您确实确实想使用CASE语句，则需要CASE返回在WHERE子句中检查的值.例如

If for some reason you really do want to use a CASE statement, you'd need the CASE to return a value that you check in the WHERE clause. For example

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.

这篇关于where子句中的用例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！