SQL DB2 - WHERE 子句中的条件逻辑
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7638042/
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
SQL DB2 - conditional logic in WHERE clause
提问by j-p
I need to pull back records based on a location ID, but I've got two fields that MAY contain the incoming criteria....
我需要根据位置 ID 撤回记录，但我有两个可能包含传入条件的字段....
something like this
像这样的东西
SELECT * FROM tbl
WHERE myVar = locationID
IF LocationID = 0
myVar = location2ID
this is a 'logical' example...
这是一个“合乎逻辑”的例子......
I think I can do
我想我可以
WHERE myVar = CASE WHEN locationID = 0 THEN location2ID ELSE locationID END
although I've read that CASE in a WHERE clause should be avoided...? why? or is this OK? - either way it FAILS
虽然我读过应该避免 WHERE 子句中的 CASE ......？为什么？或者这可以吗？- 无论哪种方式都失败
WHERE CASE WHEN locationID=0 THEN location2ID=myVAr ELSE locationID=myVar END
also FAILS
也失败
thx
谢谢
Sorry for the confusion lads - didn't mean to be "ambiguous" - looks like #2 will do what I want - but for the clarification requested here is the issue...
很抱歉让小伙子们感到困惑 - 并不是说“模棱两可” - 看起来 #2 会做我想做的 - 但对于这里要求的澄清是问题......
the table stores TWO locations, let's call then CURRENT_LOC and ORIGINAL_LOC... in most cases these will BOTH have data, but in some cases the 'thing' hasn't moved... so the CURRENT_LOC is '0'. MY issue is I'll be passing in a LOCATION ID, I want the records where CURRENT_LOC matches OR if the the CURRENT_LOC=0 then I ALSO want the ORIGINAL_LOC... where that matches...
该表存储两个位置，让我们调用 CURRENT_LOC 和 ORIGINAL_LOC ......在大多数情况下，它们都有数据，但在某些情况下，“东西”没有移动......所以 CURRENT_LOC 是“0”。我的问题是我将传入一个 LOCATION ID，我想要 CURRENT_LOC 匹配的记录，或者如果 CURRENT_LOC=0 那么我还想要 ORIGINAL_LOC...匹配的地方...
does that help the discussion? I hope.
这对讨论有帮助吗？我希望。
回答by onedaywhen
WHERE myVar = COALESCE(NULLIF(locationID, 0), location2ID)
WHERE myVar = COALESCE(NULLIF(locationID, 0), location2ID)
Alternatively,
或者，
WHERE (
(locationID <> 0 AND myVar = locationID)
OR
(locationID = 0 AND myVar = location2ID)
)
