topic Re: tELTOracleMap & tLoop: how to add dynamic where clause? in Talend Studio

tELTOracleMap & tLoop: how to add dynamic where clause?

grizmi — Mon, 25 Sep 2017 13:23:47 GMT

Hi,

Below is the basic flow:

tLoop (from 2013 -to 2017, step 1) -- tSetGlobalVar_Year --tELTOracleInput -- tELTOracleMAp -- tELTOracleOutput (insert)

In the where clause of tELTOracleMap I would like to add : Evaluation_Year = ((Integer)globalMap.get("EvaluationYear"))

Go is to generate 5 sql's:

Insert into .. select ... from .. where Evaluation_Year = 2013;

...

Insert into .. select ... from .. where Evaluation_Year = 2017;

Am I doing something wrong or is this a limitation ot tELT objects?

Thanks

Re: tELTOracleMap & tLoop: how to add dynamic where clause?

Anonymous — Tue, 26 Sep 2017 09:26:00 GMT

Could you attach a screenshot of your job and describe what's going wrong at the moment when you execute the job?

Re: tELTOracleMap & tLoop: how to add dynamic where clause?

grizmi — Tue, 26 Sep 2017 12:56:10 GMT

saying src.EVALUATION_YEAR = ((Integer)globalMap.get("EvaluationYear")) won't work

But even mapping e.g. "context.ETL_DATE = LOAD_DATE" fails in a tELTOracleMap. In a normal tMap there is no issue.

Just working for a few months with Talend so maybe I am missing something.

Re: tELTOracleMap & tLoop: how to add dynamic where clause?

Anonymous — Tue, 26 Sep 2017 13:06:30 GMT

Hi Grimzi,

Adding a context variable to the ELT<DB>Map component shouldn't be an issue.

What DOES seem very counterintuitive though is the fact that you don't need to comment out the context variable as the SQL generator will do that for you.

See below screenshot for an example (query doesn't make any sense but that'll give you an idea).

Hope this helps.

Re: tELTOracleMap & tLoop: how to add dynamic where clause?

grizmi — Fri, 29 Sep 2017 15:59:49 GMT

Hi,

It works when uning context variable in tELTOracleMap. Apperently, in the tIteration I first need to assign the key value defined in tSetGlobalVar to a context variable. Directly calling ((Integer)globalMap.get("EvaluationYear")) didn't work.

Thanks