Keeping an SQL function in a tDBInput

[stephbzr]

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?

[Anonymous]

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:

 

[stephbzr]

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? 

[Anonymous]

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.