getting the filename and date for the every last 30 minutes dynamically via tsendmail component

[Haresh]

I am trying to build a query from the db like:

select file_name,file_date from a left join b on a.id=b.id left join c on b.id=c.id

where a.name=

'TOMMY'

and file_date between

to_date

(

'10/28/2020 04:30:00 AM'

,

'mm/dd/yyyy hh:mi:ss AM'

) AND

TO_DATE

(

'10/30/2020 05:00:00 AM'

,

'mm/dd/yyyy hh:mi:ss AM'

)

so this date timerange has to be dynamic when i send out an email alerts for all the filenames along with date stored in the database and email has to go out at 1st minute of every hour for eg:at 10:01PM it should fetch the last 30 minutes(9:30PM-10:00PM) worth of files coming into the db and during the next scheduled run it should fetch the files consisting of 10:00-10:30pm date range at 10:31pm

How to use tmap /tfilterow to establish the goal.

Or is there any other easier way to do this?

[Anonymous]

This looks like you want to write dynamic SQL to retrieve your data. You can do this with a little bit of Java. The SQL that you write you will notice is contained inside two quotes ("Select * from table where something else..."). This is a Java String. As such, it can e be dynamically manipulated. So your WHERE clause can make use of the routines.TalendDate functions to precisely calculate your dates and write them into the SQL statement.

Let me know if you need any help with doing this. But first, take a look across the Community for solutions already supplied. Here are a couple of examples of posts about dynamic SQL.....

https://community.talend.com/s/question/0D53p00007vCkORCA0/how-to-pass-prepared-parameter-in-sql-query-in-talend

https://community.talend.com/s/question/0D53p00007vCtTiCAK/date-glob-variable-within-tmysqlinput