Hi all, there is a way in Talend Open Studio to write a query (in query builder) using tables from different databases or to see and edit the tMap process as a query, it's possible? thank you very much! vikr
Hi all, there is a way in Talend Open Studio to write a query (in query builder) using tables from different databases or to see and edit the tMap process as a query, it's possible? thank you very much! vikr
It is impossible to query data by only writing a query statement from different databases. You need to query data from different databases, then use a tMap to merge the data or do join.
Best regards
shong
Hi Vicker Another way could be to group temporarily your tables in one database to be able to join them in the query builder, if you really don't want to use the tMap. HTH, Elisa
Thank you shon and thank you esabot!
Now if I insert the tables as temporary tables in the same database then can I insert more than one select statement in the same query related to two of the three tables?
I got a syntax error message in the SQLBuilder.
The query code is the follow:
select Table1.code,Table1.nome
from Table1
select Table2.code,Table2.nome
from Table2
Or is there another way to do it? I'm using Access database.
Now if I insert the tables as temporary tables in the same database then can I insert more than one select statement in the same query related to two of the three tables?
It is impossble to write two or more select statement in the SQLBuilder.
If you want to get data from different tables, you should use several tAccessInput for each table. For example, you can use a tAccessInput component to query data from table1 and use another tAccessInput component to query data from table2.
Best regards
shong
Hi Shong, Yes, It's what I did. I have two tAccessInputs but I have to perform an advanced sql query to merge their data.
Let me explain my job:
I have inserted the tAccessinput twice, so I have four tAccessInput for only two databases (AccessDB1 and AccessDB2).
Then I've inserted a tMap between AccessDB1 and AccessDB2 to perform a left join, and the same thing between AccessDB2 and AccessDB1 to get all the rows of AccessDB2 that not match the first join.
Then I want perform a union of the two resulting tables and so I've inserted a tUnite component. After this I get some duplicates rows, so I've inserted a tUniqRow component. Then sort the rows and at last send the data to the output table.
The problem is that when I insert one row on DBAccess1 then I get one duplicate row on DBAccessOutput even though I've inserted tUniqRow.
tUnite component does not perform check for duplicates rows.
So how can I send to the output only unique rows?
Or is there another easier way to do all the job?
Please see images for further information.
Then I've inserted a tMap between AccessDB1 and AccessDB2 to perform a left join, and the same thing between AccessDB2 and AccessDB1 to get all the rows of AccessDB2 that not match the first join.
I don't know why you would do the left join.
If you want to get all the rows from the AccessDB1 and AccessDB2, you just need to use two tAccessInput components to get data from two DB. Then, use a tUnite component to merge all the rows and use a a tUniqRow component to the unique rows.(see the screenshot1)
Note that you should select all the columns as key attribute(see the screenshot2)
Best regards
shong
Hi shong,
I do the left join because I have these two input tables made up of three fields and one output table made up of four fields, and I want join them according to one common field.
These are my three tables:
InputTable1 (id1, code1, name)
InputTable2 (id2, code2, name)
OutputTable (id3, code1, code2, name)
So in the output table I want the common field (name) and one field (code1 and code2) for each input table.
If I use only the tUnite I can't specify the destination fields correctly as I can do with the tMap, tUnite component does a union between the input tables but it puts the "name" field in the wrong field "code2".
So Could I specify correctly the destination fields in the tUnite component?
Furthermore I've tried also to set the tUniqRow selecting all the rows as keys attributes, but however I get duplicates rows. The duplicates rows are the common rows between the input tables.
If I set only "name" as key attribute I get the duplicate rows only when I insert new rows in the input tables.
What could I do to solve these problems?
thanks!
vikr
