topic Re: Keeping an SQL function in a tDBInput in Talend Studio

Keeping an SQL function in a tDBInput

stephbzr — Fri, 15 Nov 2024 22:21:16 GMT

Hello, I would like to know if it is possible to keep a SQL function in the syntax of a tDBInput. For example :

"select data1

from hist690

where hist_datcpt = TO_DATE('value_from_tmap', 'DD/MM/RR')"

Inside the function, this is a value that I inject from my tMap. Is this possible? If not, do you have an alternative?

Re: Keeping an SQL function in a tDBInput

Anonymous — Thu, 17 Nov 2022 02:45:15 GMT

it's impossible to do so.

as instead, you can define a job context variable (e.g. date1) , setup the value for context variable in tJavaRow after the tMap component, then use it in the tDBInput query like

"select data1

from hist690

where hist_datcpt = TO_DATE('"+ context.date1+"', 'DD/MM/RR')"

here is an example:

Re: Keeping an SQL function in a tDBInput

stephbzr — Thu, 17 Nov 2022 10:32:24 GMT

Hello @Aiming Chen ,

Thank you very much for this very interesting method, but my value for the context variable varies for each input row of the tMap (the rows come from an Excel file). Will the method work? If not, how can I do it?

Re: Keeping an SQL function in a tDBInput

Anonymous — Thu, 17 Nov 2022 15:57:48 GMT

Hello,

Try to use tMap -> tFlowToIterate -- iterate --> tDBInput

"select data1

from hist690

where hist_datcpt = TO_DATE('"+( (String)globalMap.get("... use ctrl+Space for this) )+"', 'DD/MM/RR')"

Based on the number of records (there's a 2k limit for an IN caluse) you could also do it this way:

tAggregateRow -> concatenate the formatted string dates:

TO_STRING(hist_datcpt, 'DD/MM'RR') IN ( << list of dates >> )

In case you have more than 2000 dates you can still add a rowNum modulo 1000 to your dataset and submit queries with 1000 items at a time. This latter is more effort to develop but for bigger tables it works better than executing queries one by one.