SQL DB2 - WHERE 子句中的条件逻辑
我需要根据位置 ID 拉回记录，但我有两个可能包含传入条件的字段...
这样的东西
SELECT * FROM tbl
WHERE myVar = locationID
IF LocationID = 0
myVar = location2ID
是一个“逻辑”示例...
我认为我可以做到，
WHERE myVar = CASE WHEN locationID = 0 THEN location2ID ELSE locationID END
尽管我'您是否读过应该避免 WHERE 子句中的 CASE...？为什么？或者这样可以吗？ - 无论哪种方式，它都会失败，
WHERE CASE WHEN locationID=0 THEN location2ID=myVAr ELSE locationID=myVar END
谢谢
，抱歉
，小伙子们，我的困惑 - 并不是“模棱两可” - 看起来#2会做我想要的 - 但这里要求的澄清是问题......
表存储两个位置，让我们调用 CURRENT_LOC 和 ORIGINAL_LOC...在大多数情况下，它们都有数据，但在某些情况下，“东西”没有移动...所以 CURRENT_LOC是“0”。我的问题是我将传递一个位置 ID，我想要 CURRENT_LOC 匹配的记录，或者如果 CURRENT_LOC=0 那么我还想要 ORIGINAL_LOC...匹配的位置...
这对讨论有帮助吗？我希望。
I need to pull back records based on a location ID, but I've got two fields that MAY contain the incoming criteria....
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 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...
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...
does that help the discussion? I hope.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
WHERE myVar = COALESCE(NULLIF(locationID, 0), location2ID)或者，
WHERE myVar = COALESCE(NULLIF(locationID, 0), location2ID)Alternatively,