具有 NULL 值的 Oracle SQL Case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11617010/
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
Oracle SQL Case statement with NULL values
提问by Jamie Taylor
I'm trying to create an Oracle query that will use a parameter based on the CommandNameof a button.
我正在尝试创建一个 Oracle 查询，该查询将使用基于CommandName按钮的参数。
So here's my query
所以这是我的查询
SELECT id
FROM table
WHERE dateTime IS (CASE WHEN :CommandName = 'FIRST' THEN NULL ELSE NOT NULL END)
So I'm passing the parameter but it's not working - I'm just getting a Missing NULL keyworderror
所以我正在传递参数但它不起作用 - 我只是收到一个Missing NULL keyword错误
I basically don't want to have to write two separate queries based on the parameter that is input
我基本上不想根据输入的参数编写两个单独的查询
Not even sure if this is possible or if this is the right way of doing it?
甚至不确定这是否可行，或者这是否是正确的做法？
Any ideas?
有任何想法吗？
回答by John D
You may want to read up on the CASE statement a bit more.
您可能想多读一点 CASE 语句。
I believe you want the following code:
我相信你想要以下代码：
SELECT
id
FROM
table
WHERE
(
(dateTime IS NULL AND :CommandName = 'FIRST')
OR
(dateTime IS NOT NULL AND NVL(:CommandName, '') <> 'FIRST')
)
回答by Wolf
Without using dynamic SQL to return the string 'NULL' or 'NOT NULL' you could use two NVL()functions:
如果不使用动态 SQL 返回字符串 'NULL' 或 'NOT NULL'，您可以使用两个NVL()函数：
select id
from table
where NVL(dateTime,'FIRST') = NVL(:param,dateTime);
However, if dateTime is indexed (and Oracle can index NULLs), ths NVL() function will disable the index.
但是，如果 dateTime 被索引（并且 Oracle 可以索引 NULL），则 NVL() 函数将禁用索引。
回答by fudo
I think that
我觉得
SELECT id
FROM table
WHERE (dateTime IS NULL AND :CommandName='FIRST') OR (dateTime IS NOT NULL AND :CommandName <> 'FIRST')
should do what you need.
应该做你需要的。
