Make tOracleInput execute query once with tXmlMap

[elias_sayegh_foo]

Hello everyone,
I have the following job (see picture attached).

The data flow contains a list of invoices. I need to query the invoices due dates from a database.
The thing is, the tXmlMap component is making the tOracleInput component execute the query as many times as there is items in the main list. So if there is 10 invoices, the query is executed 10 times, and it's not the behavior I want (for the performance).
In the tJavaRow component, I built a where condition clause for the query, that I saved in a variable using the tSetEnv component, in order to use it in the tOracleInput component. So the query looks like this :

" select invoice_number, due_date from invoices " + System.getProperty("where_clause")

So the query will only get what I want in one shot.
However the tXmlMap is executing the query several times. How can I make it execute only once and then join the result ?

[Anonymous]

Hi,
Sorry for delay!
Could you please also post your tXMLMap component setting screenshot into forum which will be helpful for us to address your issue?
Best regards
Sabrina

[elias_sayegh_foo]

Hello Sabrina,
Many thanks for your time.
I am relatively new to Talend, and your help is highly appreciated.
This is the tXmlMap component settings.

The thing is, the query is being executed multiple times.
The behavior I want, is the query being executed one time, and the result being mapped with the data.
How can I achieve such a behavior ?
Best regards,
Elias Sayegh

[Anonymous]

You need to change the "Lookup Model" to "Load Once". This will fire the query as soon as the subjob starts and keep the data in memory instead of firing the query for every row received from the main input.

[elias_sayegh_foo]

Hello rhall_2.0
Thank you for your reply.
This is not what I need. 
The table content is very huge, millions of records, setting the lookup model to load once is taking lots of time.
It's ok for the query to be executed for every time the job is executed, but if within the same job execution, there is 10 items in the main input, the query is being executed 10 times.
The thing is I have already set a where condition for the query before the tXmlMap component, so that the query returns the appropriate data for these 10 items.
I just have to make the query execute once per job execution.

[Anonymous]

OK, I get what you mean. Setting the "Reload On Each Row" is correct but you need to supply a variable for your WHERE CLAUSE on each row. To do this you use the "GlobalMap Key" functionality. Click the green cross and create a globalMap variable. Supply it with a value from your main flow that you want to use to filter your lookup query. Then use that globalMap variable in the WHERE CLAUSE of your lookup query.
I have written a tutorial which uses this functionality. Unfortunately it is not exactly what you are doing, but you should be able to extrapolate. The tutorial is here:  https://www.rilhia.com/quicktips/quick-tip-row-multiplication
Remember that your SQL Query is just a Java String. Therefore when setting your WHERE CLAUSE you can do something like this.....

"...
WHERE mytable.column = '" + ((String)globalMap("MyValue"))+ "'"

Hope this helps

[vapukov]

Hello rhall_2.0
Thank you for your reply.
This is not what I need. 
The table content is very huge, millions of records, setting the lookup model to load once is taking lots of time.
It's ok for the query to be executed for every time the job is executed, but if within the same job execution, there is 10 items in the main input, the query is being executed 10 times.
The thing is I have already set a where condition for the query before the tXmlMap component, so that the query returns the appropriate data for these 10 items.
I just have to make the query execute once per job execution.

sorry may be I miss Your idea, 
but it is exactly what suggest real_2.0!
Your current settings - will execute query for each row

just add where conditions You can choose 2 method:
- make single select for small portion of table data, but cover all lookup record, and run query once per job
- add WHERE for return data only related for single record - and run this query for each row