tELTOracleMap & tLoop: how to add dynamic where clause?

Highlighted
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


Accepted Solutions
Highlighted
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

View solution in original post


All Replies
Highlighted
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?

Highlighted
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.

Highlighted
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

View solution in original post

Highlighted
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

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog