topic Re: How to insert data if not present in table?Infinidb in Talend Studio

How to insert data if not present in table?Infinidb

Anonymous — Sat, 16 Nov 2024 12:44:24 GMT

Hi all!
I have a table in the infinidb. Table not have foreign key.
My talend job have two tInputMySQL, - one main input and one Lookup component that connect to the tMap.
I use expession in the tMap, "row1.id!=row4.Master_category_value" If id not exist at the selected data then process insert data. But this expression don't work! Talend ignore this expression and beging insert duplicate data.
Ok, in the tMysqlOutput component set option in "action on data" = update or insert. But Talend create new row when and i have many duplicate rows! Does this issue cose i use table without foreign keys?

Sorry for my english. Lets see my screenshots in attachments.

Re: How to insert data if not present in table?Infinidb

alevy — Tue, 16 Aug 2011 00:18:04 GMT

What you have done in tMap is for each main row (row1) get every row from the lookup table (row4) where row1.id!=row4.Master_category_value whereas what you want, I think, is to get each main row that does not have a match in the lookup table.
You should have row1.id in the Expr.key field to the left of Master_category_value in the lookup table and change the lookup table to inner-join. Then remove the filter expression on the output table and change "Catch lookup inner join reject" to true.

Re: How to insert data if not present in table?Infinidb

Anonymous — Tue, 16 Aug 2011 08:49:18 GMT

Wnen i set Catch lookup inner join reject in true then 0 rows selected to the outputable..

I want create query like : select * from table a where a.id not in (select master_id from table b)
and insert there rows.
But table a and table b have different databases and i can't implement this query in one tMysqlInput
See attachments.

Re: How to insert data if not present in table?Infinidb

Anonymous — Tue, 16 Aug 2011 10:13:54 GMT

Hi
You don't configure it correctly on tMap. You need to do a inner join and get the reject rows.
Best regards
Shong

Re: How to insert data if not present in table?Infinidb

Anonymous — Tue, 16 Aug 2011 10:20:41 GMT

But Join Model for row4 have only one option: Left Outer Join and dont select to another..

Re: How to insert data if not present in table?Infinidb

Anonymous — Tue, 16 Aug 2011 10:24:35 GMT

hmm, it should be able to select another option, double click on the 'left outer join', there should be a button like '...', click on the button and select the option 'inner join'
Best regards
Shong

Re: How to insert data if not present in table?Infinidb

Anonymous — Tue, 16 Aug 2011 10:30:14 GMT

hmm, it should be able to select another option, double click on the 'left outer join', there should be a button like '...', click on the button and select the option 'inner join'
Best regards
Shong

No, Join Model for row4 have only 'left outer join' option..
talend open studio, 4.2.2 version
Build id: r63143-20110622-0628

Re: How to insert data if not present in table?Infinidb

Anonymous — Tue, 16 Aug 2011 10:33:28 GMT

Hi
Which OS?
Can you export the job and send it to me? I think there must be a job design problem.
Best regards
Shong

Re: How to insert data if not present in table?Infinidb

Anonymous — Tue, 16 Aug 2011 10:38:37 GMT

Hi
Which OS?
Can you export the job and send it to me? I think there must be a job design problem.
Best regards
Shong

Ok,
Windows 7 x64.

Re: How to insert data if not present in table?Infinidb

Anonymous — Tue, 16 Aug 2011 10:46:44 GMT

Hi
Which OS?
Can you export the job and send it to me? I think there must be a job design problem.
Best regards
Shong

Ok,
Windows 7 x64.
I have been sent email.

Re: How to insert data if not present in table?Infinidb

Anonymous — Tue, 16 Aug 2011 14:51:57 GMT

But with "Inner join" option my process doesnt work correct..
Expression being ignore.

Re: How to insert data if not present in table?Infinidb

alevy — Tue, 16 Aug 2011 23:43:45 GMT

But Join Model for row4 have only one option: Left Outer Join and dont select to another..

As you've figured out, you can't change to an inner-join before you specify the fields for the join i.e. you must enter row3.id against Master_category_value first.

But with "Inner join" option my process doesnt work correct..

You still have not followed my or shong's instructions. Only have the inner-join and "Catch lookup inner join reject". Do not have your expression (row3.id!=row4.Master_category_value) anywhere.
What you have done now is join on row3.id==row4.Master_category_value and then filter out every lookup result where row3.id!=row4.Master_category_value i.e. no results.

Re: How to insert data if not present in table?Infinidb

Anonymous — Wed, 17 Aug 2011 13:50:16 GMT

But Join Model for row4 have only one option: Left Outer Join and dont select to another..

As you've figured out, you can't change to an inner-join before you specify the fields for the join i.e. you must enter row3.id against Master_category_value first.

But with "Inner join" option my process doesnt work correct..

You still have not followed my or shong's instructions. Only have the inner-join and "Catch lookup inner join reject". Do not have your expression (row3.id!=row4.Master_category_value) anywhere.
What you have done now is join on row3.id==row4.Master_category_value and then filter out every lookup result where row3.id!=row4.Master_category_value i.e. no results.

This decision doesn't work. I want to select rows from source table with ID's which isn't present in the output table with Master_category_id's. Or if output table does not have rows then process must be insert all rows from source table to output table.

Re: How to insert data if not present in table?Infinidb

Anonymous — Wed, 17 Aug 2011 13:54:23 GMT

This is simple case, but I can't still have implement it.

Re: How to insert data if not present in table?Infinidb

rbaldwin — Wed, 17 Aug 2011 15:32:51 GMT

Based upon the most recent screenshot I've seen of your tMap, you've got it slightly misconfigured.
Here are the options that you need:
1. Join condition on row4 (row1.id) in Master_category_value, which you have.
2. Under the options, set "Join Model" equal to "Inner Join" - which you may have already, but the options on row4 aren't shown.
3. On out1's options set "Catch lookup inner join rejects" to "true"

See the attached screenshot for an example of the configuration for both the input and the output sides of the tMap.
Hope that helps.