topic Re: [resolved] Repeat query multiple times with different where condition in Talend Studio

[resolved] Repeat query multiple times with different where condition

Anonymous — Fri, 02 Jan 2015 04:04:21 GMT

Hi All,
http://www.talendforge.org/forum/img/members/207464/mini_Talend.jpeg.jpeg
I have tow redshift component for the input. I am doing a join between them in tmap and then filtering it based on some condition.
Query in my Redshift component has a where condition indicating a date range between which it needs to get data. Now I want to repeat it for multiple fixed dates. How can I automate it in Talend. Do we have any component for it?
Thanks,
Neil Shah

Re: [resolved] Repeat query multiple times with different where condition

Anonymous — Fri, 02 Jan 2015 05:32:40 GMT

Hi
I am not sure I understand your problem well, but you can try the tForEach component which can iterate each fixed dates provided by you, append the result of each iteration to the same file. For example:
tForEach--iterate--your main business processing
Best regards
Shong

Re: [resolved] Repeat query multiple times with different where condition

Anonymous — Fri, 02 Jan 2015 05:52:09 GMT

Yes, that sounds a good idea. Thanks.
But, my each iteration would have 4 dates. (It is range) which I need to specify. I guess, by default, tforeach can contain 1 value.
Referring to https://help.talend.com/search/all?query=tForeach&content-lang=en
Not sure, if it would work, but adding 4 dates in one row and then taking a substring might work. But do we have any other option?
My query in 1 component looks like "Select a,b,c from table1 where date <'2013-01-01' and date >'2012-01-01'
My query in 2nd component looks like "Select x,y,a from table2 where date <'2012-05-05' and date > '2013-02-02' "
For each iteration, all 4 date values would change.
Thanks,
Neil Shah

Re: [resolved] Repeat query multiple times with different where condition

Anonymous — Fri, 02 Jan 2015 10:55:56 GMT

Hi
You can also use tFixedFlowInput to define the input data, define 4 columns for each date and iterate each row with tFlowToIterate, for example:
parent job:
tFixedFlowInput--main(row1)-tFlowToiterate--iterate--tRunJob
tRunJob: call the child job, and pass the current 4 dates to child job, see this article to know how to pass a value from parent job to child job via context variable.
child job:'
txxxInput_1--main--tMap-->
|
txxxInput_2
on DB input component, define the query like:
"Select a,b,c from table1 where date <'"+context.firstDate+"' and date >'"+context.secondDate+"'"
Best regards
Shong

Re: [resolved] Repeat query multiple times with different where condition

Anonymous — Mon, 05 Jan 2015 02:14:19 GMT

Hi Shong,
Thanks for your reply. I somehow update the query to not include the date for second part (So Now only 2 date conditions instead of 4) and actually made it work with below method:-
tforeach -> RedshiftInput -- tmap
|
Another input
in tforeach, I put date as "2015-01-01,2014-10-01" and then in query i used it as follows
"select a,b,c
from table_a
and created_date <' "+ ((String)globalMap.get("tForeach_1_CURRENT_VALUE")).split(",")+" '
and created_date >=' "+ ((String)globalMap.get("tForeach_1_CURRENT_VALUE")).split(",")+" ' "

Re: [resolved] Repeat query multiple times with different where condition

Anonymous — Mon, 05 Jan 2015 04:19:31 GMT

Hi
Great! Thanks for your feedback!
Best regards
Shong