Oracle where 子句中的 case 表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5152294/
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
Case expression in Oracle where clause
提问by Ciaran Bruen
I have a standard search clause whereby I'm selecting records on certain filters such as description and status, the status values being 101 to 110. Status may be null, if so I return records of any status. However I now have a new status which has to be excluded from the returned records when there is no specific status, and only returned when specifically selected. So a search based on a specific status returns just that status, a search without a specific status returns all statuses except the new one. The original where clause is:
我有一个标准的搜索子句，我在某些过滤器上选择记录，例如描述和状态，状态值为 101 到 110。状态可能为空，如果是这样，我将返回任何状态的记录。但是，我现在有一个新状态，当没有特定状态时，必须从返回的记录中排除它，并且只有在特别选择时才返回。因此，基于特定状态的搜索仅返回该状态，没有特定状态的搜索将返回除新状态之外的所有状态。原来的 where 子句是：
where Upper(cfs.CAE_SEC_ID) = Upper(NVL(p_cae_sec_id_n,cfs.CAE_SEC_ID))
and Upper(SEC_CODE) like '%' || Upper(NVL(p_fm_sec_code_c,SEC_CODE)) || '%'
and APPR_STATUS = NVL(p_appr_status, APPR_STATUS)
order by appr_status DESC, cae_sec_id
What I now want to do is something like this:
我现在想做的是这样的：
where Upper(cfs.CAE_SEC_ID) = Upper(NVL(p_cae_sec_id_n,cfs.CAE_SEC_ID))
and Upper(SEC_CODE) like '%' || Upper(NVL(p_fm_sec_code_c,SEC_CODE)) || '%'
and APPR_STATUS =
(CASE WHEN p_appr_status is null THEN --return all statuses except 110
WHEN p_appr_status is not null THEN (p_appr_status)
END)
order by appr_status DESC, cae_sec_id
Is this possible with a case expression in the where clause?
这可能与 where 子句中的 case 表达式有关吗？
@Damien provided the answer so thanks to him for that. There is another scenario I need to cater for - the same proc returns individual as well as multiple records. If someone searches for an individual record (p_cae_sec_id_n is not null) that has a status of ignore then that was being excluded from above, so I've added it in below:
@Damien 提供了答案，因此感谢他。我需要满足另一种情况 - 相同的 proc 返回单个以及多个记录。如果有人搜索具有忽略状态的单个记录（p_cae_sec_id_n 不为空），则该记录已从上面排除，因此我已将其添加到下面：
and APPR_STATUS =
(CASE WHEN p_appr_status is null and APPR_STATUS != 110 THEN APPR_STATUS
WHEN (p_appr_status is null and p_cae_sec_id_n is not null) THEN APPR_STATUS
WHEN p_appr_status is not null THEN (p_appr_status)
END)
回答by Damien_The_Unbeliever
I'm more of a SQL Server guy, but the following should do the trick (assuming Oracle's not equal to is <>, not !=):
我更像是一个 SQL Server 人，但以下应该可以解决问题（假设 Oracle 不等于 is <>， not !=）：
(CASE WHEN p_appr_status is null and APPR_STATUS<>101 THEN APPR_STATUS
WHEN p_appr_status is not null THEN (p_appr_status)
END)
