Need to populate the one to many relationship values(one to many)

One Star

Need to populate the one to many relationship values(one to many)

 
Here my issue is: Need to populate the one to many relationship values
 
:\Users\cnander\AppData\Local\Temp\msohtmlclip1\01\clip_image002.jpg
 
 
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
 
Example:
 
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
 
:\Users\cnander\AppData\Local\Temp\msohtmlclip1\01\clip_image004.jpg
 
 
Table Name: X_CUSTOMERLINEAGE
 
:\Users\cnander\AppData\Local\Temp\msohtmlclip1\01\clip_image006.jpg
 
 
 
 
Table Name: X_CUSTOMERLINEA1632383797
 
:\Users\cnander\AppData\Local\Temp\msohtmlclip1\01\clip_image008.jpg
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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.
 
:\Users\cnander\AppData\Local\Temp\msohtmlclip1\01\clip_image010.jpg
 
 
:\Users\cnander\AppData\Local\Temp\msohtmlclip1\01\clip_image012.jpg
 
 
 
 
Twelve Stars

Re: Need to populate the one to many relationship values(one to many)

pictures not attached, could You edit Your post?
-----------
Five Stars

Re: Need to populate the one to many relationship values(one to many)

Hi,
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.
Pat