i have a job. there are severl tables composed of this job. my question is like this.. select a.a1, a.a2, a.a3 b.b1 from AAA a, BBB b -->1 where a.timefield='20120101' --> 2 and a.a1 = b.b1 --> 3
with this example query, if there are 100 row in table AAA and 1000 rows in table BBB and there are 10 row in table AAA with the condition of a.timefield='20120101' and there are 20 rows in table BBB with the condition of a.a1=b.b1, the total row might be 10 rows. table AAA is a driving table. and BBB is lookup table.
if i run it with talend, talend load all data of BBB(Lookup) first, regardless of condition(a.a1 = b.b1).
i want to make lookup table load data according to the mapped condition in tMap. not loads lookup table first. my case looks like there are 5 lookup tables and in each of lookup table, there are 10 miliion data. but i will insert 5000 data from main table. i want to change it into the way of loading lookup data according to the main data. also as u see my pics, when talend loads data from lookup tables, at some point, it produce an error. Exception in component java.util.ConcurrentModificationException at java.util.LinkedList$ListItr.checkForComodification(Unknown Source) at java.util.LinkedList$ListItr.next(Unknown Source) at routines.system.RunStat.sendMessages(RunStat.java:248) at routines.system.RunStat.stopThreadStat(RunStat.java:225) at damc.lis_test_0_1.LIS_TEST.tOracleInput_3Process(LIS_TEST.java:6319) at damc.lis_test_0_1.LIS_TEST.tOracleInput_1Process(LIS_TEST.java:2613) at damc.lis_test_0_1.LIS_TEST.runJobInTOS(LIS_TEST.java:9051) at damc.lis_test_0_1.LIS_TEST.main(LIS_TEST.java:8919) disconnected
The only way to not load all data in the lookup is to have a WHERE clause in the SQL for that lookup. If you change the lookup model in tMap to "Reload at each row", then you can set the WHERE clause based on a value from each incoming main row. Please read scenario 6 of the tMap help to see how this works. Note that is very slow for more than a few rows as the SQL is executed for each row. Make sure the WHERE clause acts on an index to improve performance.