asked on
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
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
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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.