Forum Stats

  • 3,872,062 Users
  • 2,266,374 Discussions
  • 7,911,043 Comments

Discussions

How to implement this logic in ODI 12c mapping?

Sudipta Gupta
Sudipta Gupta Member Posts: 128
edited Jun 30, 2018 12:36PM in Data Integrator

Hi Experts,

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.

DIM_ACCOUNT

==============

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

STAGING TABLE

==============

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.

Tagged:
3173245

Best Answer

  • Pavan8u
    Pavan8u Member Posts: 278 Silver Badge
    edited Jun 19, 2018 12:35AM Answer ✓

    Simplest way is to use ODI procedure.

    If you want to implement using mappings create reusable mapping for this-

    SELECT MAX(RECORD_LOAD_DATE) from STAGING_TABLE WHERE ACCOUNT_ID=ACCOUNT_ID and EXPIRY_DATE>=RECORD_LOAD_DATE)

    Use aggregate component to get max(record_load_date).

    Use this reusable mapping in the main mapping.

    Note- Aggregate can be used in main mapping as well.

    Please proceed as per your convenience.

    Regards,

    Pavan

    3173245

Answers

This discussion has been closed.