Hi everybody, I would want to know if it is possible and how to filter rows compared to the foreign key. For example, we have 2 tables : Table : Customer (id_customer, etc..) Table : Order (id_order, id_costumer (FK)). I would like to insert only orders whose "id_customer" exist. Thanks a lot in advance!
Hi, You have to control before to insert (in tMap). However, if you don't have much records and the FK is a real constraint on database, you may want to keep the things simple and just push all the records - those for which the customer doesn't exists will be rejected. Regards, TRF
Thanks for your reply but it really didn't help me^^ I have 42 000 rows to insert and I have seen the filter option in tMap component but don't know how to link the filter to the FK. Someone else please?
Thanks a lot TRF! Your answer helped me because I didn't know it yet but it doesn't resolve my problem; it doesn't give the result I expected. Maybe I was not clear in my question : I would want to know if it is possible and how to filter rows compared to the foreign key. For example, we have 2 tables : Table : Customer (id_customer, etc..) Table : Order (id_order, id_costumer (FK)). I would like to insert only orders whose Order.id_customer exist in the table Customer as id_customer.
Can you now help me further? Thanks a lot in advance!
That's exactly what this tMap allow. On the right side of the tMap configurator, select False for Catch lookup inner join reject. With this option, only orders associated to a customer will be retrieved on out1 (or row4 in the following capture). That's what you expect.
OK, I'm quite confused then because there are 39 201 lines (not 42 000 actually) in the CSV file (before mapping), 38 863 after mapping and only 6 142 rows inserted in the DB. I suppose that logically, 38 863 rows might be injected, isn't it? Indeed, while checking the data in the DB, I realized that a lot of rows which must be injected was not... I have tried again by changing Lookup Model and Match Model but no more results.. Anymore help? Thanks in advance!
You have 38 863 records pushed to the database. If only only 6142 rows are available in the table at the end, you probably have errors during the insert. Add a tLogRow after tMysqlOutput. In the tMysqlOutput component, go to Advanced setting and uncheck "Extend insert" (Insertions étendues). Right click on tMysqlOutput then Row, Rejects and click on tLogRow.
Arrange tLogRow at your convenience (table for example) then Run. You shoud have the rejected records on the console with the according error messages. Regards, TRF
Okay, I had to uncheck 'Die on error' in order to drag a reject row. The result is the same because I have no error Fed up^^' That said, I had errors with outer join then I think that I can try with outer join, and logging rejecting rows...
The error is that the foreign key constraint fails and I suppose that data were overwritten because I tried to truncate table and then "insert" in Talend instead of "insert or update", 38 863 rows have been inserted.
I wanted to filter rows basedon foriegnkey constraints. I'm using posgresql DB. SO, using inne rjoin I'm able to filter the rows based on constraint. But I also need the list of rejected rows. SO to see the reject rows, I'm using ROw---> Rejectedrows from tpostgresqloutput option. But I'm unable to get the records.