Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How between Clause works and boundry conditions

Hello All,
I am not sure how boundary conditions works for between clause.
My requirement is to fetch incremental records from system.
If I am running my extraction logic on first of every month then it should fetch all records till last day of previous month.
After completion of my fetch logic , I am updating variable #ohapah.ETL_PREMIUM_END to current date so that I can fetch records starting from that day (which is first of every month) till last day of the month.
I guess below condition should work fine for me and boundary dates would be included ?
TRANSACTION_DT BETWEEN to_date('#OHAPAH.ETL_LOAD_END','YYYYMMDD') and sysdate -1.
I am wondering about scenario when some one has accidently triggered the logic twice on same day (very first day of the month). In that case for example, I ran the load twice on 1st Apr 2020 then
First time extraction criteria: TRANSACTION_DT BETWEEN (01-MAR-2020) and 31-MAR-2020 (variable #ohapah.ETL_LOAD_END= 01-MAR-2020).
For Second load run on the same day: #ohapah.ETL_LOAD_END will become 01-APR-2020 and hence extraction Criteria:
TRANSACTION_DT BETWEEN (01-APR-2020) and 31-MAR-2020
In this case all records pertaining to 31-MAR-2020 would be extracted again? or nothing will be extracted?

Thanks,
Rajneesh

This post has been answered by Frank Kulash on Dec 21 2020
Jump to Answer
Comments
Post Details
Added on Dec 21 2020
1 comment
835 views