oracle 如何在 where 子句中使用 NVL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36616245/
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
How to use NVL in where clause
提问by ZerOne
I have the following code:
我有以下代码：
SELECT cr_ts, msg_id, info
FROM messaging_log
WHERE UPPER(INFO) LIKE '%' || TRIM(UPPER(P_INFO)) || '%'
AND cr_ts >= NVL(P_DATE, SYSDATE-10)
AND msg_id = NVL(P_ID, msg_id)
ORDER BY cr_ts desc;
where P_INFO, P_DATE and P_ID are parameters. In the normal case every parameter is NULL. But then the where clause AND msg_id = NVL(P_ID, msg_ID)don't return the rows where msg_id itself is NULL. What did I miss?
其中 P_INFO、P_DATE 和 P_ID 是参数。在正常情况下，每个参数都是 NULL。但是 where 子句AND msg_id = NVL(P_ID, msg_ID)不返回 msg_id 本身为 NULL 的行。我错过了什么？
How to return all entries when P_ID and msg_id are NULL?
当 P_ID 和 msg_id 为 NULL 时如何返回所有条目？
采纳答案by Blank
Try this;)
尝试这个;）
SELECT
cr_ts,
msg_id,
info
FROM messaging_log
WHERE UPPER(INFO) LIKE '%' || TRIM(UPPER(P_INFO)) || '%'
AND ((P_DATE IS NULL AND cr_ts >= (SYSDATE - 10)) OR cr_ts >= P_DATE)
AND (P_ID IS NULL OR msg_id = P_ID)
ORDER BY cr_ts DESC;
回答by Adesh
SELECT cr_ts, msg_id, info
FROM messaging_log
WHERE UPPER(INFO) LIKE '%' || TRIM(UPPER(P_INFO)) || '%'
AND cr_ts >= NVL(P_DATE, SYSDATE-10)
AND NVL(msg_id,'*') = NVL(P_ID, '*')
ORDER BY cr_ts desc;
回答by Vishal5364
If msg_id is NULL, then you cannot compare a NULL value using an equal (=) operator.
如果 msg_id 为 NULL，则不能使用等号 (=) 运算符比较 NULL 值。
