Hi group,
I know this has been asked multiple times, but I simply don't grasp using a case statement inside a WHERE clause. So I need some help:
My current WHERE clause reads:
a16.FULL_DATE between (SELECT (TRUNC(SysDate - 8)) From Dual) and (Select(TRUNC(SysDate - 2)) From Dual)
However I need to "automate" this a bit based on the current date/day of the month. I need my case statement to say:
(Case when to_char(sysdate-3,'mm') <> to_char(sysdate,'mm')
then a16.FULL_DATE between (SELECT(trunc(ADD_MONTHS((LAST_DAY(SysDate-3)),-1)+1) and (SELECT(add_months(trunc(SysDate-2),-1)) from Dual)
Else a16.FULL_DATE between (SELECT trunc(ADD_MONTHS((LAST_DAY(SysDate)),-1)+1) From Dual) and (Select(TRUNC(SysDate - 2)) From Dual)
However I have an error on "Else". Can someone explain what is wrong and how to fix this? I've tested both the "Then" and "Else" syntax and both will run individually. So I'm pretty sure it doesn't like my CASE statement in general.
In advance, thanks for your help.
Don