topic Re: how to insert and update from multiple join table to single table in Talend Studio

how to insert and update from multiple join table to single table

Anonymous — Fri, 01 Jun 2012 19:30:57 GMT

Hi,
Is there any way to migrate data from multiple join table to single table using insert and update operation.
Notes:
database:MSSQL 2008 R2
Sample Input Table
Table bookingCard (bookingCardId(PK), bookingCardNo,bookingStatus);
Table bookingProduct(bookingProductId(PK), bookingCardId, productGroup);
Table bookingSubProduct(bookingSubProductId(PK),bookingProductID,SalesAmount);
Sample Output Table
Table BookingReport (bookingReportId(PK), bookingCardId,bookingProductId,bookingSubProductId,productGroup,SalesAmount);

I must use left join. Because there may be bookingCard but no product or subproduct. Update can be happened on bookingStatus, productGroup or SalesAmount.
I have spent quite a long time to do insert and update operation in a different way but failed. Specially problem appears on bookingReportId primary key.
I expect Talend can handle this situation. I hope someone can write the steps to achieve this.
Thanking you. Best regards.
Asif

Re: how to insert and update from multiple join table to single table

Anonymous — Mon, 04 Jun 2012 04:10:54 GMT

Hi Asif
I guess the job logic in tMap is not correct.
Could you show us an example? What's your input data? What's your expected data?
Then we can create a job for you.
Regards,
Pedro

Re: how to insert and update from multiple join table to single table

Anonymous — Mon, 04 Jun 2012 07:39:46 GMT

Thank you so much for your attention.
Ok, here is the example. I make it very simple. If this can be done, I hope rest I can follow. There are two input tables 1) shippingCard and 2) shippingProduct are joined(in my case 9 tables to be joined) and output table is the shippingReport table. shippingCardId field is used to join shippingProduct and shippingCard Tables.
Table shippingCard
shippingCardId(pk);shippingCardNo;shippingStatus
Table shippingProduct
shippingProductId(pk);shippingCardId(fk);productGroup;billingmethod;
Output Table; shippingReport
shippingReportId(pk);shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
CASE1; Create shipping Card
shippingCardId(pk);shippingCardNo;shippingStatus
1;BC101;ACTIVE
output in shippingReport Table:
shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;NULL; BC101;null;null; (insert operation in output table)
CASE2; Create shipping Product
shippingProductId;shippingCardId;productGroup;billingmethod;
1;1;AIR;CASH
Output:
shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;1; BC101;ACC;CASH (update operation in output table)
CASE3 ; Create another shipping Product (insert operation in output table)
shippingProductId;shippingCardId;productGroup;billingmethod;
2;1;ACC;CASH
Output:
shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;1; BC101;AIR;CASH
2;1;2;BC101;ACC;CASH
CASE4 ; Update productGroup from CASH TO CHEQUE
shippingProductId;shippingCardId;productGroup;billingmethod;
2;1;ACC;CHEQUE
Output:
shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;1; BC101;AIR;CASH
2;1;2;BC101;ACC;CHEQUE
Thanks.
Asif

Re: how to insert and update from multiple join table to single table

Anonymous — Mon, 04 Jun 2012 08:00:32 GMT

Hi
You might create a job as the following images.
Case 4: you'd better regard shippingCardId and shippingProductId as combination key for update.
Regards,
Pedro

Re: how to insert and update from multiple join table to single table

Anonymous — Mon, 04 Jun 2012 12:08:32 GMT

I tried you example but cannot make it work. Please see images.
I have created two rows in bookingCard Table.Run the job. Successfully created two lines in shippingReport.
shippingCardId shippingCardNo shippingStatus
1 BC101 ACTIVE
2 BC102 ACTIVE

Then created one line in shippingProduct.
shippingProductId shippingCardId productGroup billingMethod
1 1 AIR CASH

It generates duplicate insert error.
Thanks.
asif

Re: how to insert and update from multiple join table to single table

Anonymous — Tue, 05 Jun 2012 03:09:41 GMT

Hi Asif
The reason that you got duplicate key is because Numeric.sequence("s1",1,1) starts from 1 every time and there are some rows in your DB table already.
You might save the value of last primary key in DB table into a delimited file and extract it every time you run the job.
Numeric.sequence("s1",context.lastPK,1)
Regards,
Pedro

Re: how to insert and update from multiple join table to single table

Anonymous — Fri, 20 Jul 2012 06:30:37 GMT

Hello Pedro,
Would you please help , how can I add delete operation along with insert and update operation.
scenerio:
Source table has data deleted -> target table will also delete the same data.
FYI, at this time, our migration project job, tMap has match model->Unique match, join model->Inner Join and ForInsert Catch lookup inner join reject=true and ForUpdate, Catch lookup inner join reject=false.
These works fine. Now suddenly situation arises, I need to implement delete operation with this condition. What can the tmap settings.
Thanking you.
Asif

Re: how to insert and update from multiple join table to single table

Anonymous — Fri, 20 Jul 2012 06:55:34 GMT

Hi Asif
You would see the option "delete" of "Action on data" of any DB output component.
Plus: if you want to delete records, the table needs to have primary key.
Regards,
Pedro

Re: how to insert and update from multiple join table to single table

Anonymous — Fri, 20 Jul 2012 07:17:35 GMT

Thanks pedro.
But you didn't write output table property value for delete operation
Catch output reject = ?
Catch lookup inner join reject = ?
Thanks.
asif

Re: how to insert and update from multiple join table to single table

Anonymous — Fri, 20 Jul 2012 07:24:26 GMT

Hi Asif
I don't understand.
Let's say you want to delete data in mysql.
If you want to delete a record in one table, just tell tmysqlOutput the id which you want to delete.
Catch output reject = ?
Catch lookup inner join reject = ?
These two options depend on your own job logic.
Regards,
Pedro

Re: how to insert and update from multiple join table to single table

Anonymous — Fri, 20 Jul 2012 07:45:24 GMT

i am extrememly sorry. let me explain you.
I have source table and target table.I read data from target table and keep in hash. Then I have tMap where source table is main and hash is lookup. Then tMap settings inner join and unique match model. tMap output link has two mssqloutput, one for insert and one for update. For Inert I have the above logic catchlookup inner join reject=true and false for update.

see the job design

Targettable -> tHashoutput
|
|
SourceTable \
\
tMap ----------tmssqloutput(forInsert - catch lookup inner join reject = true)
/ \
hashInput/ \
-------------tMssqlOutput(forUpdate - catch lookup inner join reject = false)
I wanted to know if add another link from tMap to mssqloutput for delete operation what should be the condition
catchlookup inner join reject.
Thanks.
Asif

Re: how to insert and update from multiple join table to single table

Anonymous — Fri, 20 Jul 2012 07:53:08 GMT

Hi Asif
Sorry.
I understand what you say above. But I don't know what data you want to delete.
Regards,
Pedro

Re: how to insert and update from multiple join table to single table

Anonymous — Fri, 20 Jul 2012 08:03:03 GMT

My Source and Target table both are same. Everytime data inserted or updated in the source table my etl job insert or update same data in target table migrating from source table.
So operatios are
if source table has a new record. my etl job insert the rows to the target table.
if source table has updates some fields of any rows, etl job also update fileds of target table.
I want if source table data is deleted, etl job will also delete target table rows.
Thanks again.
asif

Re: how to insert and update from multiple join table to single table

Anonymous — Fri, 20 Jul 2012 08:06:11 GMT

Hi Asif
Got you.
In fact, what you need is called Capture Data Change feature which is only supported by Talend Enterprise Edition.
For TOS, what you can do is to record all the ids before they are deleted in source table.
Regards,
Pedro

Re: how to insert and update from multiple join table to single table

Anonymous — Fri, 20 Jul 2012 08:49:25 GMT

Dear pedro,
We cannot use CDC feature now. However, I set scheduler in every five minutes and using some other logic such as last modified date of each table.Insert and update operations are fine at this moment.
Is it possible to create another job to check source table ids and target table ids and then delete those ids in target table which are not found in source table. Any idea of job settings.
Thanks.
Asif

Re: how to insert and update from multiple join table to single table

Anonymous — Fri, 20 Jul 2012 08:54:07 GMT

Hi Asif
Yes. It's possible.
Just create a new job and do inner join with the source table and target table.
Then you will get the ids which have been deleted.
Regards,
Pedro

Re: how to insert and update from multiple join table to single table

Anonymous — Mon, 21 Dec 2015 04:00:43 GMT

Hi All,
I m using TOS 5.6.1. I build the job with more inner join and outer join in tmap. when i m using that error table and output table data are not coming properly. please any one guide me can i get the all data with one error and main table