Inner join without lookup

One Star

Inner join without lookup

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.
One Star

Re: Inner join without lookup

Hi,

Use an SQL query on your tInputDB.
Jeremie
One Star

Re: Inner join without lookup

Hi,
Use an SQL query on your tInputDB.
Jeremie

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.
One Star

Re: Inner join without lookup

Has anyone had any luck with this. Cannot for the life of me get this to work.
Four Stars

Re: Inner join without lookup

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
One Star

Re: Inner join without lookup

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
Four Stars

Re: Inner join without lookup

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.

Amirths
One Star

Re: Inner join without lookup

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.
Community Manager

Re: Inner join without lookup

Hello bnye
..but how can I do the same thing with a left outer join?

Please see 1659 and 2202 to learn how to do a left outer join and get unmatched rows.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Inner join without lookup

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.
One Star

Re: Inner join without lookup

Why not just use ONE tOracle Input and put your whole query in it - Joins and everything - let the database do all the work.
Yes - it won't visually show you that you are joining tables - but it would be a small price to pay.
Employee

Re: Inner join without lookup

Hi all,
My best advice is to use the tELTOracleInput for every tables, a tELTOracleMap to perform your JOIN and transformation via Oracle database then a tELTOracleOutput to store result of the ELT transformation.
ELT transformation generates a SQL query to perform operations and transformation, it's easy to design and very fast and efficient.
Best regards.
One Star

Re: Inner join without lookup

SMaz -
The reason for the second Input is that I have a join to my ADDRESS table in the primary query via Input1. This query links to the sequence 1. But for this output file I also need the address information from sequence 2. This is not possible in one query.
cantoine -
I will give this a try next.
One Star

Re: Inner join without lookup

Hi all,
My best advice is to use the tELTOracleInput for every tables, a tELTOracleMap to perform your JOIN and transformation via Oracle database then a tELTOracleOutput to store result of the ELT transformation.
ELT transformation generates a SQL query to perform operations and transformation, it's easy to design and very fast and efficient.
Best regards.

cantoine -
How do you then map the ELTOracleMap to my output file?
It looks like the tELTOracleOutput stores the result in a table in the database. I was hoping not to have to create temporary tables for this. Is that correct.
One Star

Re: Inner join without lookup

It appears that the ELTOracleInput and ELTOracltMap method won't work. This is very frustrating. This is one of the most basic functions of an ETL tool. I should have the ability to perform an unlimited number of queries by chaining together Inputs and tMaps, but there is no such thing as a join in the tmap only a lookup.
One Star

Re: Inner join without lookup

Check out the following post: http://www.talendforge.org/forum/viewtopic.php?id=5762

This will allow you call a single select for each row, passing in the lookup value and use the result in your output. Basically, it's tOracleInput -> tOracleRow -> tParseRecordSet -> -> tOracleOutput

hth,

Thomas
One Star

Re: Inner join without lookup

I went to the training and they demonstrated how to get this done. Basically its as follows:
tOracleInput1 -> row1 -> tFlowToIterate -> Iterate -> tOracleInput2 -> row2 -> tMap -> tFileOutput
The tFlowToIterate places everything from Main1 into the globalMap then when in the SQL statement on the tOracleInput2 you can add a where clause that specifies a unique idea like WHERE UNIQUEID = " + row1.UniqueID. Finally in your tMap while you only see the schema from Main2 you can still use the items in row1. You just don't get to see the nice lines from the input schema to the output schema. The only other catch is that if you want to show values from row1 even where there are no values in row2 you need to select from the primary table of tOracleInput1 in tOracleInput2 and left outer join your connecting table and put all other where clauses in the left outer join itself, otherwise you turn your left outer join into an inner join. Here is the exact tOracleInput2 that I used in this job. Notice the second selector ADR_SEQ_NO in the join and not in the WHERE clause.
"SELECT DBR.DBR_NO, ADR.ADR_DBR_NO, ADR.ADR_SEQ_NO, ADR.ADR_NAME, ADR.ADR_ADDR1,
ADR.ADR_ADDR2, ADR.ADR_CITY, ADR.ADR_STATE, ADR.ADR_ZIP_CODE,
ADR.ADR_PHONE1, ADR.ADR_PHONE2, ADR.ADR_DOB_O FROM CDS.DBR LEFT OUTER JOIN CDS.ADR
ON DBR.DBR_NO = ADR.ADR_DBR_NO AND ADR.ADR_SEQ_NO = '02'
WHERE DBR.DBR_NO = '" + row1.DBR_NO + "'"