You are not logged in.

Unanswered posts



Important! This site has been replaced. All content here is read-only. Please visit our brand-new community at https://community.talend.com/. We look forward to hearing from you there!



#1 2006-11-19 21:09:52

raffaele
Guest

raffaele said:

join or outer join in a tMap

How can I choose between "join" or "outer join" in a tMap? I would like to make a join between two tables
but only rows for which to join works should get out from the operator...

#2 2006-11-19 23:15:00

plegall
Talend Team


plegall said:

Re: join or outer join in a tMap

I think you mean "inner join" or "outer join" :-). Let me say briefly what is the difference with an example.

As input, we have an "articles" list:

id;name
1;pizza
2;pasta

.. and a "sales" list, with a reference to the "articles":

article_id;shop;quantity
2;shopA;10
23;shopB;23

The join is on sales.article_id and articles.id. The main data source is here the "sales" list.

With an "inner join", the output would not contain the second line because there is no article 23 is the "articles" list. With an "outer join" (a "left outer join" to be very precise), the output would contain the second line of "sales" but the article name would be null.

In Talend Open Studio, the tMap will have as main input the "sales" and as lookup input, the "articles" list. By default, the join is an "outer join". If you want to simulate an "inner join" you have to add a "condition constraint" in the output. In the example I take in this post, the condition would be to check if the name of the article is defined:

defined $rowN[article_name]

Offline

#3 2006-11-20 08:49:02

raffaele
Guest

raffaele said:

Re: join or outer join in a tMap

OK, thanks the end of your post gives me the answer I needed

#4 2006-11-20 08:53:20

raffaele
Guest

raffaele said:

Re: join or outer join in a tMap

Anyway, it would be nice if the choice between inner join or outer join were configurable in the t_Map parameters...

#5 2006-11-20 09:22:17

raffaele
Guest

raffaele said:

Re: join or outer join in a tMap

plegall wrote:

In Talend Open Studio, the tMap will have as main input the "sales" and as lookup input, the "articles" list. By default, the join is an "outer join". If you want to simulate an "inner join" you have to add a "condition constraint" in the output. In the example I take in this post, the condition would be to check if the name of the article is defined:

defined $rowN[article_name]

I tried but I cannot let it work. I have a tMap with two inputs. The main comes from another tMap, the lookup is a table named "Risorse". I join the two imputs on the field ID_RISORSA, which is primary key of Risorse table, then I put the constraint
defined $Risorse[ID_RISORSA] in the tMap output. When I run the job I get the following error:

DBD::mysql::st execute failed: Column set to default value; NULL supplied to NOT NULL column 'RES_IDENT' at row 1 at C:\progetto_ETL_Khalix\TOS-Win32-20061004-1934-V1\TOS-Win32-20061004-1934-V1\workspace\.Perl\PROVA.process_testjob.pl line 1481.
can't execute insert query
Job testjob ended at 09:12 20/11/2006. [exit code=2]

Without constraints, that is to say by using the outer join feature, everything goes allright and the rows ae written in the target table. But, I need the inner join feature.
Maybe it would be better if you put a further configuration parameter in the tMap, allowing to choose whether to make inner join or outer join. The writing of perl code whould be reduced to the minimum, since not everybody is an expert Perl programmer...And the  philosophy of graphical ETL tools like Talend should be to avoid people, as much as possible,  knowing SQL or scripting languages in order to create ETL processes...

#6 2006-11-21 10:44:27

raffaele
Guest

raffaele said:

Re: join or outer join in a tMap

Hallo, any idea on the reason of the error message I put to your attention in my previous post? (see below).
Thanks

#7 2006-11-21 12:17:24

plegall
Talend Team


plegall said:

Re: join or outer join in a tMap

Run Process wrote:

DBD::mysql::st execute failed: Column set to default value; NULL supplied to NOT NULL column 'RES_IDENT' [...]

It looks a lot like a consequence of [Bug] 200, the correction will be delivered in release 1.0.1

raffaele wrote:

Maybe it would be better if you put a further configuration parameter in the tMap, allowing to choose whether to make inner join or outer join. The writing of perl code whould be reduced to the minimum, since not everybody is an expert Perl programmer...And the  philosophy of graphical ETL tools like Talend should be to avoid people, as much as possible,  knowing SQL or scripting languages in order to create ETL processes...

We've talked about it with amaumont this morning and he'll make it. Create a feature request and amaumont will take it in charge.

Offline

Board footer

Talend Contributor Agreement - Talend Website Privacy Policy