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

Checking for each row if the key exists in another table

Anonymous — Sat, 16 Nov 2024 13:26:13 GMT

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)

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

Anonymous — Tue, 11 May 2010 09:36:10 GMT

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

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

Anonymous — Wed, 12 May 2010 14:44:22 GMT

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.

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

Anonymous — Wed, 12 May 2010 15:13:27 GMT

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.

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

Anonymous — Wed, 12 May 2010 16:36:53 GMT

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

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

Anonymous — Wed, 12 May 2010 18:13:43 GMT

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