topic Re: Dynamic query using timestamp in Talend Studio

Dynamic query using timestamp

Anonymous — Tue, 16 May 2017 14:04:01 GMT

New to this forum and tool.

I have been able to generate a simple job to read from mySQL and extract it into CSV.

However the source side data being huge I need to extract the same data in different date ranges like "date between <one_date> and <another_date>".

I would however like to set these date ranges in a table / file which can then be picked up in a loop to execute for a different date range every time till the end of record / file is reached. The CSV will be created in the append mode.

Any pointers to a similar question asked previously would be helpful.

-A

Re: Dynamic query using timestamp

cterenzi — Tue, 16 May 2017 15:03:43 GMT

If you have your start date and end date in a data source (file or table both work), you can use a tFlowToIterate to execute a subjob once for each row in your input. The values for each row are converted to global variables which you can reference in the subjob. Read up on tFlowToIterate here:
https://help.talend.com/#/reader/wDRBNUuxk629sNcI0dNYaA/WqJCkTE5~5DDaSFvtNXZnQ

Re: Dynamic query using timestamp

Anonymous — Tue, 16 May 2017 16:49:11 GMT

Created a flow using the documentation link.

However getting the error as seen below. The date format seems to be incorrect.

The date format in the file is 'yyyy-MM-dd' that is used to set the global variable.

Re: Dynamic query using timestamp

cterenzi — Tue, 16 May 2017 20:02:27 GMT

You will likely want to format the date to a value accepted by your database. TalendDate.formatDate("yyyy-MM-dd", <variable goes here>)

Ex:

TalendDate.formatDate("yyyy-MM-dd",row6.DATE)

Drop your global variable in as the second argument and it should work better.