oracle Where 子句中的 if 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15396217/
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
If statement within Where clause
提问by user2100620
I am working with a query which contains "IF" statements within a "WHERE" clause. But PL\SQL Developer is giving some errors while executing it. Can anyone please help me with the correct query? Here is the query:
我正在处理一个在“WHERE”子句中包含“IF”语句的查询。但是 PL\SQL Developer 在执行时出现了一些错误。任何人都可以帮助我进行正确的查询吗？这是查询：
SELECT t.first_name,
t.last_name,
t.employid,
t.status
FROM employeetable t
WHERE IF status_flag = STATUS_ACTIVE then t.status = 'A'
IF status_flag = STATUS_INACTIVE then t.status = 'T'
IF source_flag = SOURCE_FUNCTION then t.business_unit = 'production'
IF source_flag = SOURCE_USER then t.business_unit = 'users'
AND t.first_name LIKE firstname
AND t.last_name LIKE lastname
AND t.employid LIKE employeeid;
I receive the error "ORA-00920: invalid relational operator".
我收到错误“ORA-00920：无效的关系运算符”。
Placing brackets around status_flag = STATUS_ACTIVEresults in error "ORA-00907: missing right parenthesis"
将括号status_flag = STATUS_ACTIVE放在导致错误“ORA-00907：缺少右括号”
采纳答案by DCookie
CASE might help you out:
CASE 可能会帮助你：
SELECT t.first_name,
t.last_name,
t.employid,
t.status
FROM employeetable t
WHERE t.status = (CASE WHEN status_flag = STATUS_ACTIVE THEN 'A'
WHEN status_flag = STATUS_INACTIVE THEN 'T'
ELSE null END)
AND t.business_unit = (CASE WHEN source_flag = SOURCE_FUNCTION THEN 'production'
WHEN source_flag = SOURCE_USER THEN 'users'
ELSE null END)
AND t.first_name LIKE firstname
AND t.last_name LIKE lastname
AND t.employid LIKE employeeid;
The CASE statementevaluates multiple conditions to produce a single value. So, in the first usage, I check the value of status_flag, returning 'A', 'T' or null depending on what it's value is, and compare that to t.status. I do the same for the business_unit column with a second CASE statement.
该CASE语句评估多个条件，以产生一个单一的价值。因此，在第一次使用时，我检查 status_flag 的值，根据它的值返回 'A'、'T' 或 null，并将其与 t.status 进行比较。我使用第二个 CASE 语句对 business_unit 列执行相同操作。
回答by John Kugelman
You can't use IF like that. You can do what you want with AND and OR:
你不能像那样使用 IF。你可以用 AND 和 OR 做你想做的事：
SELECT t.first_name,
t.last_name,
t.employid,
t.status
FROM employeetable t
WHERE ((status_flag = STATUS_ACTIVE AND t.status = 'A')
OR (status_flag = STATUS_INACTIVE AND t.status = 'T')
OR (source_flag = SOURCE_FUNCTION AND t.business_unit = 'production')
OR (source_flag = SOURCE_USER AND t.business_unit = 'users'))
AND t.first_name LIKE firstname
AND t.last_name LIKE lastname
AND t.employid LIKE employeeid;
