Checking for each row if the key exists in another table

One Star

Checking for each row if the key exists in another table

Hi,
I have looked over the forum for and answer to this and couldn't find it so there it is :
Basically, from a result set, I'm gettint the number of distinct month involved then after that, I'm checking into another table for each different month found, if it exist. If so, I want to update the row and if now, insert it.
I tought my logic was good lookin at my schema but I guess not since each time, it inserts (and crash on duplicate key) the row and never updates it when it already exists. Beside, it actually seems to not iterate at all even If I used an iterate link.
I have put some screenshot of my component values for the part of the schema that buggs me.
Any help would be appreciated.
Thank you,

(the first 4 pictures are not good, but deleting them doesn't seems to work)
One Star Lie
One Star

Re: Checking for each row if the key exists in another table

Hi
I didn't understand all of your problem, but maybe this can help :
- Try to use the tJoin component to get rejected (no month corresponding) or filtered rows. Then perform your operations for each flow ?
- Try to specify the "Update or insert" property of tMySQLInput ?
Hope that'll help.

Regards,
Lie
One Star

Re: Checking for each row if the key exists in another table

Hi Lie,
Thank you for your answer.
From what I can understand, the TJoin component is used to check a table against another one (a sql join) but what I'm trying to do is check for a value within a WHERE clause so I guess this is not a valid option for me.
For your second suggestion, I already used that property in my current schema but my problem is now before that point (I have already fixed a portion of it), when it is time to look up for the specified value within the table not on the action to take when found or not.
Maybe I wasn't clear enough on my first post so here is my detailled situation :
1) I'm running a query to get all the distinct month from a table column.
2) For each of these row, I want to run another query that will go check in another table if a row already exist with that specific month or not.
3) So far, the problem is that even if I have more than 1 different month (and thus, more than 1 row to iterate through), my "iterate" link doesn't seems to work. When looking at the flow, it seems that talend only iterate it once and not once for each row previously found (distinct month).

Hope I made myself clearer.
Thanks in advance.
One Star Lie
One Star

Re: Checking for each row if the key exists in another table

I think I see your problem :
Instead of :
tMysqlInput_20 --Iterate--> tMysqlInput_9

Use :
tMysqlInput_20 --row1--> tFlowToIterate --Iterate--> tMysqlInput_9

On your tMysqlInput_9, you have :
SELECT WHERE MONTH(currentMonth) == " + row20.currentMonth

Try :
SELECT WHERE MONTH(currentMonth) == " + ((String)globalMap.get("row1.code"))

(with row1 being the flow name between tMysqlInput_20 and tFlowToIterate)

The iterate link from tMysqlInput only increments a counter, and doesn't store retrieved datas.
One Star

Re: Checking for each row if the key exists in another table

OK, I see the big picture.
However, should I not be using the TMysqlRow component instead of my TMysqlInput_9 ?
One Star

Re: Checking for each row if the key exists in another table

Finally, I think I got it by adding a TForeach component between the TFlowIterate and the TMysqlInput component.
Thank you for the lead Lie.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now