topic Re: Inner join without lookup in Talend Studio

Inner join without lookup

Anonymous — Sat, 16 Nov 2024 14:00:29 GMT

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.

Re: Inner join without lookup

Anonymous — Mon, 06 Apr 2009 10:28:38 GMT

Hi,

Use an SQL query on your tInputDB.
Jeremie

Re: Inner join without lookup

Anonymous — Mon, 06 Apr 2009 17:39:07 GMT

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.

Re: Inner join without lookup

Anonymous — Tue, 07 Apr 2009 23:05:29 GMT

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

Re: Inner join without lookup

Anonymous — Wed, 08 Apr 2009 06:03:06 GMT

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

Re: Inner join without lookup

Anonymous — Wed, 08 Apr 2009 18:26:46 GMT

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

Re: Inner join without lookup

Anonymous — Thu, 09 Apr 2009 05:09:47 GMT

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

Re: Inner join without lookup

Anonymous — Fri, 10 Apr 2009 01:13:15 GMT

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.

Re: Inner join without lookup

Anonymous — Fri, 10 Apr 2009 03:22:38 GMT

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

Re: Inner join without lookup

Anonymous — Fri, 10 Apr 2009 05:49:47 GMT

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.

Re: Inner join without lookup

Anonymous — Fri, 10 Apr 2009 17:18:22 GMT

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.

Re: Inner join without lookup

Anonymous — Fri, 10 Apr 2009 17:38:16 GMT

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.

Re: Inner join without lookup

Anonymous — Fri, 10 Apr 2009 18:03:28 GMT

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.

Re: Inner join without lookup

Anonymous — Fri, 10 Apr 2009 20:15:24 GMT

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.

Re: Inner join without lookup

Anonymous — Fri, 10 Apr 2009 22:24:00 GMT

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.

Re: Inner join without lookup

_AnonymousUser — Sat, 11 Apr 2009 01:05:21 GMT

Check out the following post: https://community.talend.com/t5/Design-and-Development/SQL-query-on-tMap-output/td-p/94559

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

Re: Inner join without lookup

Anonymous — Sat, 06 Jun 2009 23:40:19 GMT

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 + "'"