I would like to join my main input with another query, but I dont want to use a lookup because the lookup pulls 2 million records before joining with the main query by ID. I would rather hit the database a few hundred times to get the records for the second input. Is this functionality possible. I do like the functionality of the tMap and would like use it for the transformation.
Yes, I already have a query on my tOracleInput_1. Now I need to add a tOracleInput_2, but if I do that as shown it creates a lookup. The lookup functionality is great for 50 states. But for our address table it will result in an initial pull of 2 million rows. That is not the way I want to perform my sub query. I would like to query the database with tOracleInput_2 for each row in tOracleInput_1 and pass it the unique ID to find. That would result in a faster tranformation in this particular case.
Thanks Ben.
Hai,
Hope this solves u r problem.
1. Main table fetch (tOracleInput)
2. tFlowToIterate
3. Sub table fetch (tOracleInput)
Starting job bnye at 10:26 08/04/2009.
connecting to socket on port 3510
connected
123|BNYE|20000|D1|SALES
456|AMIRTHS|10000|D2|PROD
disconnected
Job bnye ended at 10:26 08/04/2009.
Amirths -
almost...
But I need fields from both the Main Table Fetch and the Fetch Sub Record to be in the output file. It seems to be a problem with the tMap capabilities?
Is there anyway to resolve this scenario?
Thanks for your help.
bnye
Bnye,
Please go thru the image 2. The first four fields are from the Main table and the 5th one is from the Sub table.
Just map all the 5 fields thru tMap and then to tOracleOutput.
This is working great and I realize I said inner join...but how can I do the same thing with a left outer join?
The reason is that I have a couple of scenarios where I need the Main Table Fetch rows even if no row exists in the Fetch Sub Row query.
Thanks again for your responses.
Thank you for the response shong. However, this was my original problem. Its that this is a lookup that loads the entire Input2 and then performs the join to each row in the Input1 based on that. I was hoping to query the database for each row in Input1 rather than loading the entire address table. Thanks again for all of your help. I hope I explained the problem adequately.
I have shrunk the address table query as much as possible for this transformation and the lookup still loads 27000 records before performing the tranformation for a single input1 record. I wont always be this lucky at limiting the size of the Input2 query. Please find the image below.
Perhaps I am missing something simple regarding your tMap setup.
