Pass o/p col of tOracleInput as filter condition to another tOracleInp

[Anonymous]

Hi,
I am new to Talend. We have a requirement to implement functionality of an existing Oracle procedure into Talend and need some guidance on same. 
Database: Oracle
Talend OS : 5.2.2 
Below is logic of Procedure
1. We have 4 tables (A,B,C,D). A,B,C are input tables while D is output table. D is populated using data from A,B,C in the procedure
2. Table A and B are joined together to form an Oracle cursor. The output of this cursor is then given in the where clause of table C.
For e.g. The cursor definition is as below
CURSOR C1 IS
SELECT COL1, COL2 FROM A,B WHERE A.COL = B.COL; 
Once this cursor is opened value of COL1 is then passed to table C.
SELECT * FROM C WHERE C.COL1 = C1.COL1 AND ROWNUM <=1;
Values from Cursor C1 and table C are then passed to table D.
Currently we have formed the cursor by using SQL override of tOracleInput whereby i have joined A,B. Next we have used a tmap to join Cursor C1 with table C. Cursor C1 returns 100 records however table C even after presence of join with C scans all records in the table(C has 10000 records). This increases the overall execution time of the job
The requirement is if Cursor C1 returns 100 records only those records should be scanned from table C and only 1 record should be fetched from C for condition C.COL1 = C1.COL1 .
Can you please help me out as in how to implement the same in Talend

[willm1]

The way the tMap component works is to assign the first flow or row into it as the Main row, and subsequent rows as Lookups. In the default case, Lookups are read once per execution (can be configured to refresh for each Main row in tMap settings). Because you made C1 (100 records) the Main row and Table C (10000 rows) the lookup, Talend cycles through 10000 rows in memory (or disk) for every row in C1 or Main. 
One suggestion would be to flip your design around - make Table C the main, and the smaller table C1 your lookup. Another suggestion is to use a tflowtoIterate from the smaller dataset to a second tOracleInput leveraging a global variable - see screenshot below...

[Anonymous]

Hi Willm,
Many thanks for your reply. However we have a scenario whereby we need to use few columns of the cursor C1 along with columns from table C to populate into our main output table. 
So my main table will contain few columns from Cursor C1 and few from C. That was reason why we were using Tmap to map columns from input and output. I am not sure whether we can do same using tflowiterate. I am checking with GlobalMap key present in properties of input(via Tmap) however still not able to succeed in same