在where子句中使用case表达式

what I wanted is to add another condition using case statement or something.

我想要的是使用case语句或其他东西添加另一个条件。

I tried:

我试过了：

SELECT * FROM tblcust 
WHERE cust_id = :p_cust_id 
CASE WHEN :p_first_name IS NOT NULL THEN
        AND first_name = :p_first_name
     WHEN :p_last_name IS NOT NULL THEN
        AND last_name = :p_last_name
     WHEN :p_first_name IS NULL AND :p_last_name IS NULL THEN
        NULL
     END;

3 个解决方案

#1

You may not be able to use a CASE expression here to represent your logic, but it can certainly be reworded:

您可能无法在此处使用CASE表达式来表示您的逻辑，但它当然可以重写：

SELECT * FROM tblcust 
WHERE
    cust_id = :p_cust_id AND
    ((:p_first_name IS NOT NULL AND first_name = :p_first_name) OR
     (:p_last_name IS NOT NULL AND last_name = :p_last_name)    OR
     (:p_first_name IS NULL AND :p_last_name IS NULL));

But this is ugly, and we might able to use a COALESCE trick here to make it simpler:

但这很难看，我们可以在这里使用COALESCE技巧使其变得更简单：

SELECT * FROM tblcust 
WHERE
    cust_id = :p_cust_id AND
    (COALESCE(:p_first_name, first_name || ' ') = first_name) OR
     COALESCE(:p_last_name, last_name || ' ') = last_name) OR
     (:p_first_name IS NULL AND :p_last_name IS NULL));

The reason your CASE expression is structured incorrectly is because you are making it generate logical expressions, when it is only allowed to generate values. But, in this case, we don't even need a CASE expression to handle your logic.

CASE表达式结构不正确的原因是因为当它只允许生成值时，它会生成逻辑表达式。但是，在这种情况下，我们甚至不需要CASE表达式来处理您的逻辑。

#2

I am not sure what you want logically , but you can do case inside a case like

我不确定你想要什么逻辑，但你可以在一个案例中做案例

With a 
as (
Select 1 id , NULL col1 union all
Select 2,10 union all
Select 3,Null
)
Select * from a
where id =1
OR  Case When col1 is not null then id=2
End

#3

To make theses "mutually exclusive" sets of conditons use parentheses plus OR

为了使这些“互斥”的条件使用括号加OR

SELECT * FROM tblcust 
WHERE (:p_first_name IS NOT NULL AND first_name = :p_first_name) 
OR (:p_last_name IS NOT NULL AND last_name = :p_last_name)
OR (:p_FIRST_name IS NULL AND :p_last_name IS NULL AND cust_id = :p_cust_id)

If there are still more conditions to add the be careful to contain all the sets within an "catch all" pair of parentheses

如果还有更多条件要添加，请小心包含“全部”括号内的所有集合

#1