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
* Oracle PL/SQLGolangMonday

Avatar of undefined
Last Comment
bassman592
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
bassman592
Flag of United States of America image

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

Can you provide some sample data (just relevant fields will do) and the expected results from that data?
Avatar of bassman592
bassman592
Flag of United States of America image

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

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

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

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of bassman592
bassman592
Flag of United States of America image

ASKER

Thank you. It would be nice to be able to run a query there, but I guess everything has its limits.
Golang
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo