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.