Where clause problem in Oracle 8i - Can you "Where case when then..."
2010-07-26 13:42 Tracy. 阅读(745) 评论(0) 编辑 收藏 举报To begin with the CASE expression - the key point is that CASE is an expression, not a statement - it has to return a value. Your CASE looks like this:
Code:
where case When d = 2 then o.adddte between date1 and date2 when d = 3 then o.adddte between date3 and date4 when d IN (1, 4, 6, 5, 7) then o.adddte between date5 and date6 end
This is incorrect use of a CASE. In fact, you could be using OR:
Code:
where ( ( d = 2 and o.adddte between date1 and date2 ) or (d = 3 and o.adddte between date3 and date4) or (d IN (1, 4, 6, 5, 7) and o.adddte between date5 and date6) )
To use CASE, your WHERE clause would have to be:
Code:
where o.adddte between case When d = 2 then date1 when d = 3 then date3 else date5 end and case When d = 2 then date2 when d = 3 then date4 else date6 end
The other problem is the invalid expressions in your SQL like this one:
to_date ('trunc(sysdate-4) 15:00', 'dd/mm/yyyy hh24:mi')
This should be:
to_date(to_char(trunc(sysdate-4),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi')
or more simply:
trunc(sysdate-4)+15/24
本文来自博客园,作者:Tracy.,转载请注明原文链接:https://www.cnblogs.com/tracy/archive/2010/07/26/1785226.html