[resolved] Repeat query multiple times with different where condition

One Star

[resolved] Repeat query multiple times with different where condition

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

Accepted Solutions
One Star

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

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(",")+" ' "

All Replies
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

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(",")+" ' "
Community Manager

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

Hi
Great! Thanks for your feedback!
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business