Here my issue is: Need to populate the one to many relationship values
As shown in the Above Screen :
One Customer can have multiple Billing address, the same functionality is working through UI and we Observed the some of points at DB le level how the data is saving..
Source table Data Base: Hive Data Base Source Table Name: Cust
Target Data Base: Oracle Target table name : MST_CUSTOMER
Lookup Tab DB: Oracle Lookup Table Name: AUD_Lineage
Customer Table defined one Foreign key column X_FKPARTNER_X_TALEND_ID while we are saving the AUD_Lineage records in UI the FK is updating this column and at the same time two other relationship tables (X_CUSTOMERLINEAGE, X_CUSTOMERLINEA1632383797) here X_ CUSTOMERLINEAGE table maintains the X_TALEND_ID which is same for X_FKPARTNER_X_TALEND_ID from customer table and at the same time X_CUSTOMERLINEA1632383797 table will maintains the 3 COLUMNS X_TALEND_ID,X_FK_PARTNER_X_IDAUDPARTNER AND POS
HERE X_TALEND_ID IS unique value for multiple AUD_PARTNER values and X_FK_PARTNER_X_IDAUDPARTNER will store the PK value AUD_PARTNER and POS value incrementing w.r.t multiple values.
Table Name: MST_CUSTOMER
Table Name: X_CUSTOMERLINEAGE
Table Name: X_CUSTOMERLINEA1632383797
So To implement the same functionality through JOB I followed and observed the Below Scenario:
Step1: Source table (cust) having one customer and same customer having 4 Billing address in AUD_PARTNER table
So here I used cust table as main and AUD_Lineage table as Lookup
When I used the AUD_Lineage as Lookup in tamp if I enable INNERJOIN with ALLMATCHES based on customer it will come out as 4 records and the same 4 records are loading into target table(MST_CUSTOMER) or if I enabled in tamp with Leftouterjoin with unique match only one record is coming out and missing 3 records from lookup table by using above 2 logic one to one mapping is working fine .
Here I tried with use partial update option in tmdmoutput for above 2 scenarios but it is not working. So please suggest how to achive this functionality through DI job.
Last edited by cnander (2016-11-04 03:32:34)
Basically you can't treat 1-N or 0-N relation in one step, except if you iterate (inefficient).
Your inner join cross customer and billing adresses and return the expected result : <billing adresses> lines with adresse and customer data. So if you want to insert new customer, you must first insert(composition|agregation link) or verify/retrieve (agregation link) adresses, and in the next step insert customer with adresses references (adresses id are added in flow when you choose option return ids). To add new adress to an existing customer you can check option "Use partial update"
You can use your inner join step as source for inser/update.