Five Stars

tELTOracleMap & tLoop: how to add dynamic where clause?

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

Tags (3)
1 ACCEPTED SOLUTION

Accepted Solutions
Six Stars

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

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.

 

Screen Shot 2017-09-26 at 14.05.15.png

4 REPLIES
Six Stars

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

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

Five Stars

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

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

CaptureTalend.JPG

 

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.

Six Stars

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

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.

 

Screen Shot 2017-09-26 at 14.05.15.png

Five Stars

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

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