troubleshooting Question

How to use Calculated Date in CASE Expression

Avatar of bassman592
bassman592Flag for United States of America asked on
* Oracle PL/SQLGolangMonday
8 Comments1 Solution86 ViewsLast Modified:
I am trying to use a date range in a CASE expression:
SELECT *
FROM TableA
 WHERE status = 3
       AND (TRUNC (proc_date) <= SYSDATE
       AND TRUNC (proc_date) >=
           TRUNC (
               CASE
                   WHEN (TO_CHAR (SYSDATE, 'd') = 1
                         OR TO_CHAR (SYSDATE, 'd') = 2)
                   THEN
                       select NEXT_DAY(TRUNC(SYSDATE), 'Friday') - 7 FROM DUAL
                       --SYSDATE - 3              -- If Sunday or Monday, go back to prev friday
                   ELSE
                       SYSDATE - 1              -- Tues thru Sat
               END))  


So want to limit proc_date to between today and yesterday, except that if today is Sunday or Monday I want to use last Friday.

In the statement above, I get the error "00936 missing expression" in the SELECT in the CASE.

What am I doing wrong?

4/8/20 - I uploaded an example TableA. If the query is run today, It should return 4 records.
TableA.xlsx
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros