Connect two tables from different DBs

[Anonymous]

Hello!

I want to convert this query in a talend job and store it in a table. Currently both tables are in DB1. But RESOURCE table had moved from DB1 to DB2.

I have query like:

with filterLength as
(select distinct len(Filter_Element_ID) Len_filter
from DB1.CALCULATION where len(Filter_Element_ID) is not null and System = 'A')
select s.PROJECT_ID
, r.Calculation_ID
, s.ACTIVITY_RUB4
, ACTIVITY_RUB5
, r.Filter_Element_ID
, r.Filter_B_TYPE
, f.Len_Filter
, SUBSTRING( ACTIVITY_RUB4, 1, f.Len_Filter)
from DB1.RESOURCE s
join filterLength f on 1=1
join DB1.CALCULATION r
on SUBSTRING( ACTIVITY_RUB4, 1, f.Len_Filter) = r.Filter_Element_ID 
and f.Len_filter = len(r.Filter_Element_ID) 
and System = 'A'  
and (Filter_B_TYPE = s.ACTIVITY_RUB5 or Filter_B_TYPE is null)

I created two DBInputs and know I have to use tMap. But unable to implement the above logic. Need help in understanding how to do it in Talend.

Regards

Priyadarshini

[Anonymous · Accepted solution]

As I can see on your description, you have one SQL Request that picks information from two databases.

It's probably means, you have a DB_LINK between the two databases and so, from one DB, you can launch the sql request from a database I will call DB1.

1. You open connection to the database you will use to launch the resquest
2. tDBInput to launch the sql
3. tMap to adapt the output from sql request to your final table shema
4. tDBOutput to insert the data in the final table

I hope this will help

[Anonymous]

Currently both tables are in DB1 but RESOURCE table has moved from DB1 to DB2.

[Anonymous]

And the DBs lies on same server.

[Anonymous]

@priyadarshiniv 

Thanks for contacting through message for this query but I could see that @dgm01  has already provided the answers.

The most easy way is to use a DB link so that you can retain the original SQL. Instead of table name, you will have to use tablename@DBlink to make things work.

Please refer the below links to understand more about this concept.

https://stackoverflow.com/questions/28368876/query-table-from-another-oracle-database

https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12083

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Thank you for your reply. I am using SQL database. Can I use DB link ?

Regards
Priyadarshini

[Anonymous]

what do you think about this?

https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-s...

[Anonymous]

I hope this will help