topic Re: [resolved] Iterate over different dates, using date in SQL query in Talend Studio

[resolved] Iterate over different dates, using date in SQL query

Anonymous — Tue, 27 Jan 2015 01:53:18 GMT

Hi,
I have a query which looks like
Select x,y,z from table A JOIN table B ON x=x and "date" between startdate and enddate
This "date" parameter is an incremental date parameter which would repeat for around 150 sequential days(EG - 2014-08-01 to 2015-01-27).
I thought of using tForeach component, but it would be difficult to manually enter all 150 days in each row.
Is there any other way?
Thanks

Re: [resolved] Iterate over different dates, using date in SQL query

Anonymous — Tue, 27 Jan 2015 03:36:10 GMT

Hi
- I don't understand your requirement well, so I can't give you an precise answer, but if you want to populate the interval days between startdate and enddate, you can use TalendDate.differDate() function.
- tLoop can be used to do iteration operation.
- If you need to add date, use TalendDate.addDate() function.
Best regards
Shong

Re: [resolved] Iterate over different dates, using date in SQL query

Anonymous — Tue, 27 Jan 2015 03:42:46 GMT

Hi Shong,
Here is the requirement.
I have a query for which date is like a parameter and the query needs to be repeated for each dates for 150 days. So sequence could be
Select x,y,z from table A JOIN table B ON x=x and "2014-08-01" between startdate and enddate
Select x,y,z from table A JOIN table B ON x=x and "2014-08-02" between startdate and enddate
Select x,y,z from table A JOIN table B ON x=x and "2014-08-03" between startdate and enddate
.
.
.
Select x,y,z from table A JOIN table B ON x=x and "2015-01-25" between startdate and enddate
I would be using this query in tRedshift component. So I should be able to add this parameter in the query.

Re: [resolved] Iterate over different dates, using date in SQL query

Anonymous — Tue, 27 Jan 2015 04:04:02 GMT

tLoop--iterate--tJava--oncomponent-->tYourDBInput--main--tLogRow
On tLoop, set the loop from 1 to 150 with Step 1.
on tJava:
Context.YourDateParameter=TalendDate.addDate( Context.YourDateParameter, Context.YourDateParameter-1 , ""dd");
//Define a context variable YourDateParameter and set a default value to it or load its value at run time.
on tYourDBInput, define the query as:

"Select x,y,z from table A JOIN table B ON x=x and '"+context.YourDateParameter+"' between startdate and enddate"

Shong

Re: [resolved] Iterate over different dates, using date in SQL query

Anonymous — Tue, 27 Jan 2015 04:16:39 GMT

Hi Shong,
Thanks for reply.
I am actually trying out the same steps.
tSetGlobalVar_1 -- tLoop -- TJava -- tSQL
The only problem that I am working on currently, is passing parameters.

In tSetGlobalVar_1 , I have set startDate = "2014-08-01"

tJAVA contains this

String dt_string = "";
Date current_dt = TalendDate.parseDate("yyyy-MM-dd", (String)globalMap.get("startDate"));
current_dt = TalendDate.addDate(current_dt, 1, "dd");
dt_string = current_dt.toString();
globalMap.put("startDate", dt_string);

It is throwing me error, when I run it. I suspect the reason is format of current_dt variable.
When I print "current_dt" on screen, this is what i get "Fri Aug 01 00:00:00 SGT 2014"
When I convert "current_dt" to string and set GlobalVar "startDate", it actually stores in this format and in next loop when it retrieves the values, it fails to parse
as parse expects "yyyy-MM-dd" format

Re: [resolved] Iterate over different dates, using date in SQL query

Anonymous — Tue, 27 Jan 2015 04:26:47 GMT

dt_string = current_dt.toString();

If you print dt_string after converting date to a string, you will see it is not the format " yyyy-MM-dd ", change this code to

dt_string=TalendDate.formatDate("yyyy-MM-dd", current_dt);

Shong

Re: [resolved] Iterate over different dates, using date in SQL query

Anonymous — Wed, 28 Jan 2015 01:47:46 GMT

Thank you Shong