topic Re: Query from different databases in Talend Studio

Query from different databases

Anonymous — Sat, 16 Nov 2024 14:27:34 GMT

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

Re: Query from different databases

Anonymous — Fri, 15 Feb 2008 08:46:20 GMT

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

Re: Query from different databases

Anonymous — Fri, 15 Feb 2008 13:36:09 GMT

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

Re: Query from different databases

Anonymous — Mon, 18 Feb 2008 11:26:20 GMT

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.

Thank you!
Vikr

Re: Query from different databases

Anonymous — Tue, 19 Feb 2008 06:12:08 GMT

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

Re: Query from different databases

Anonymous — Tue, 19 Feb 2008 08:55:16 GMT

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.

Thaks in advance
vikr

Re: Query from different databases

Anonymous — Wed, 20 Feb 2008 07:49:57 GMT

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

Re: Query from different databases

Anonymous — Wed, 20 Feb 2008 09:39:52 GMT

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

Re: Query from different databases

Anonymous — Thu, 21 Feb 2008 07:05:27 GMT

Hi
Can you show some input data from InputTable1 and InputTable2 ? What are your expected result in OutputTable?
Best regards
shong

Re: Query from different databases

Anonymous — Thu, 21 Feb 2008 08:01:43 GMT

Yes, Shong
please see attached image.
Table1 and Table2 are input tables, Table3 is the output one.

Thank you
Vikr

Re: Query from different databases

Anonymous — Fri, 22 Feb 2008 18:56:24 GMT

Actually I think I should do a FULL OUTER JOIN between the tables instead of two left join, but probably I must write the code to do this.
Or could I solve this problem from Talend Open Studio IDE?
thanks
vikr

Re: Query from different databases

Anonymous — Mon, 25 Feb 2008 06:42:01 GMT

Hi
Here is job:
tFileInputDelimited_1and tFileInputDelimited_2 do a left join.
tFileInputDelimited_3 and tFileInputDelimited_4 do an inner join and get the unmatched rows.
tFileInputDelimited_1=tFileInputDelimited_4:
16;11111;Mary
17;22222;Elise
18;44444;Eric
19;55555;Shong
tFileInputDelimited_2=tFileInputDelimited_3:
1;mmmmm;Mary
2;eeeee;Elise
3;ccccc;Catly
4;ddddd;Shong
5;fffff;Plegall
Result:

Starting job formerge at 14:32 25/02/2008.
.--+-----+-----+-------.
|      tLogRow_1       |
|=-+-----+-----+------=|
|id|code1|code2|name   |
|=-+-----+-----+------=|
|1 |11111|mmmmm|Mary   |
|2 |22222|eeeee|Elise  |
|3 |44444|null |Eric   |
|4 |55555|ddddd|Shong  |
|5 |null |ccccc|Catly  |
|6 |null |fffff|Plegall|
'--+-----+-----+-------'
Job formerge ended at 14:32 25/02/2008.

Re: Query from different databases

Anonymous — Tue, 26 Feb 2008 09:40:33 GMT

Hi Shong,
yes, you are rigth
now it's working!
Thank you very much
regards
vikr

Re: Query from different databases

_AnonymousUser — Thu, 06 Oct 2011 07:33:32 GMT

Thank you very much

Re: Query from different databases

_AnonymousUser — Wed, 29 Jan 2014 12:19:02 GMT

hello
can i compare data from 2 different databases(oracle and sql server)?
plz if yes how?

Re: Query from different databases

Anonymous — Wed, 29 Jan 2014 12:53:44 GMT

hi khalidinformatic,
Yes you can get data from two different databases using the respective components toracleinput (for data from oracle database) and tmsmsqlinput (for data from sql server) and then join/compare them in the tmap.