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

One Star

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

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

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

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

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

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

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

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

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

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

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

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

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

Thank you Shong

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads