RODBC: merge tables from different databases (channel)

Question

I'm using RODBC package to connect to Oracle databases from R but I didn't succeed in merging tables from different databases without "downloading" the tables (I don't want to download them as they are too big!). I'd like to use something like:

DBa=odbcConnect(dsn="DatabaseA",uid="uid",pwd="pwd",readOnly="True")
DBb=odbcConnect(dsn="DatabaseB",uid="uid",pwd="pwd",readOnly="True")
sqldf("select a.year, sum(b.var) as sumVar
       from sqlFetch(DBa,'tableA') a
            sqlFetch(DBb,'tableB') b
       where a.ID=b.ID
       group by a.year")

If someone has an idea, it would be really helpful! Many thanks in advance.

Lionel

Answer 1

This question is similar to the question here. The answer seems to be that RODBC cannot access two different databases in a single query, using sqlQuery(...), because the connection (channel) is database-specific. So either

(1) do it using downloads (as in your code), or 
(2) have your DBA put both tables in a single database, or 
(3) use something other than R. 

Answer 2

In Netezza it is working fine using sqlQuery function.

require("RODBC")

ch <- odbcConnect("NZSQL")

dim.cust.acc1  <- sqlQuery(ch,"Select * from DB1..DIM_ACCOUNT a inner join DB2..BASE_201707 b on a.id_number=b.id_number limit 1000",believeNRows = FALSE)

Answer 3

I cannot comment other answares or upvote @abdul mohammad, but he is right. I just made a complex query merging up to 4 different databases. check your sintax. you can call a different database like

select * from TableInTheConnection A
left join [server ip].TableInOtherConnection B 
...