- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
How to implement this logic in ODI 12c mapping?
I'm new to ODI 12c, but not new to ETL and Data warehousing.
I'm trying to create a mapping to update a couple of new columns added to an existing Dimension (SCD Type 2).
LOGIC: Each record in account dimension, check the Expiry Date and look for the last record loaded in staging table prior to that expiry date. Simplified example recordset gave below.
record_no account_id effective_date expiry_date new_flag (NEW)
1 1234 08-AUG-10 17-APR-17 null
2 1234 17-APR-17 01-MAY-18 null
record_load_date account_id flag
16-OCT-11 1234 Y
19-JUL-14 1234 N
19-JUL-14 1234 Y
27-MAR-17 1234 N
How can I implement the above logic in ODI 12c mapping? Any help would be really helpful.
I already created a mapping by joining above two tables with ACCOUNT_ID and EXPIRY_DATE>=RECORD_LOAD_DATE.
But how I can implement another condition like..... AND EXPIRY_DATE = (SELECT MAX(RECORD_LOAD_DATE) from STAGING_TABLE WHERE ACCOUNT_ID=ACCOUNT_ID and EXPIRY_DATE>=RECORD_LOAD_DATE)
Thanks in advance.