5

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

Jilber Urbina
  • 58,147
  • 10
  • 114
  • 138
user3062082
  • 51
  • 2
  • sqldf is for using SQL on data frames. It uses a database as the backend to do this but it is not intended for accessing databases (although with some effort it can be done but that is not the intended application). In any case it does not support Oracle, RODBC or ODBC. It supports SQLite ()default), H2, MySQL and PostgreSQL. – G. Grothendieck May 29 '21 at 12:01

3 Answers3

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.
jlhoward
  • 58,004
  • 7
  • 97
  • 140
0

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)
abdul mohammad
  • 11
  • 2
0

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 
...
Alejo
  • 315
  • 1
  • 10