Using a Case Statement in the Where Clause

[kq]

Hi - I have a query that runs automatically on weekdays to pull in the previous day's activities -- which I get by sysdate-1. On Monday's, I'd like to change that to sysdate-3 to get the previous Friday's data.

Here's my original criteria.
AND (
TO_CHAR(EVNT_CORP_RECVD_DT, 'YYYY-MM-DD') BETWEEN
TO_CHAR(SYSDATE-7, 'YYYY-MM-DD') AND TO_CHAR(SYSDATE-1, 'YYYY-MM-DD')

And here's my failed attempt!
AND (
TO_CHAR(EVNT_CORP_RECVD_DT, 'YYYY-MM-DD') BETWEEN
CASE WHEN TO_CHAR(SYSDATE,'D') = 2 THEN TO_CHAR(SYSDATE-3, 'YYYY-MM-DD') AND TO_CHAR(SYSDATE-1, 'YYYY-MM-DD')
ELSE TO_CHAR(SYSDATE-1, 'YYYY-MM-DD') AND TO_CHAR(SYSDATE-1, 'YYYY-MM-DD')
END

I get a "missing keyword" error message. Thanks for your help.

 

[SantaMufasa]

KG,

Your code is close. Also, you (or Oracle) is doing more work than necessary with all of the "TO_CHAR" functions...Since your expressions are already DATE data types, you can use that to your advantage. Here is code that I recommend that should work:

...AND trunc(EVNT_CORP_RECVD_DT) BETWEEN  
  CASE WHEN TO_CHAR(SYSDATE,'D') = 2
       THEN trunc(SYSDATE)-3
       ELSE trunc(sysdate)-1 end
  AND  trunc(sysdate)-1;

Let us know if this meets your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kq]

Thanks! Took your suggestions about simplifying the code, and everything works like a dream!