Link to home
Start Free TrialLog in
Avatar of bassman592
bassman592Flag for United States of America

asked on

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
Avatar of awking00
awking00
Flag of United States of America image

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?
Avatar of bassman592

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?
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.
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
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you. It would be nice to be able to run a query there, but I guess everything has its limits.