How to use Calculated Date in CASE Expression
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
Last Comment
awking00
Just for clarification, If today is Sunday or Monday, you want records from what day through what day and if today is any other day, you want records from what day through what day?
ASKER
As a general rule, I always want records from yesterday. However, if today is Sunday or Monday, I want records from the previous Friday. If today is Saturday, I would want records from yesterday (Friday), just like any other day. Basically, data only comes in Mon-Fri, but the query could be run on any day. I'm only checking for one day because the data files that come in are cumulative, not deltas.
Can you provide some sample data (just relevant fields will do) and the expected results from that data?
ASKER
I just uploaded an example of TableA. If run on 4/8/20, the query should return 4 records. The one with a proc_date of 10/31/19 would not be returned.
I can understand why the record with a proc_date of 10/31/2019 is not returned. What I don't understand is why the 4/8/2020 records are returned if the query is run on 4/8/2020 and you state that you want only yesterday's records. Your example also doesn't show what should happen if the query is run on a Sunday or Monday. Perhaps you could expand your example to include more records with proc_dates covering more than a week and what you would expect on if the query is run on Wednesday or Sunday or Monday.
ASKER
I should have said yesterday OR today. The filter is:
WHERE status = 3
AND (TRUNC (proc_date) <= SYSDATE...
Also, I realized I didn't include the status column in the example table. You can either add a column called status and set the values for all records to 3, or remove the status filter from the query, it's not relevant to my question.
My real question is this: why can't I run this query inside the CASE statement:
THEN
select NEXT_DAY(TRUNC(SYSDATE), 'Friday') - 7 FROM DUAL
WHERE status = 3
AND (TRUNC (proc_date) <= SYSDATE...
Also, I realized I didn't include the status column in the example table. You can either add a column called status and set the values for all records to 3, or remove the status filter from the query, it's not relevant to my question.
My real question is this: why can't I run this query inside the CASE statement:
THEN
select NEXT_DAY(TRUNC(SYSDATE), 'Friday') - 7 FROM DUAL
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you. It would be nice to be able to run a query there, but I guess everything has its limits.
Golang
Golang, also called Go, is an open source programming language that is a statically-typed language with syntax loosely derived from C, adding automatic memory management, type safety, some dynamic-typing capabilities, additional built-in types such as variable-length arrays and key-value maps, and a large standard library. Go is a general-purpose systems programming language that aims to be efficient both for development and execution with a focus on fast compilation and increased maintainability of large projects. Go was originally targeted at systems programming tasks such as building server/web applications, high throughput middleware and databases.
1K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
