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