unable to run sql query against oracle table in R

问题:

I can run this query in sqlplus against an oracle table, it works I get results back:

SELECT * FROM KEYNOTE_PRD WHERE KEYNOTE_PRD."Site"='High Frequency NY Traffic'

but I try to same with the following code within R:

tryCatch({
  ch=odbcConnect("<id>",pwd = "<passwd>")
  sql<-c("SELECT * FROM KEYNOTE_PRD WHERE KEYNOTE_PRD."Site"='High Frequency NY Traffic'")
  res<-sqlQuery(ch, sql)

},error = function(e) {
  print(odbcGetErrMsg(ch))

  print("retrive or connect to the db")
})
odbcClose(ch)

It does not work. I think it does not like the double quotes within double quotes (KEYNOTE_PRD."Site"). Any ideas how would I get around this?

回答1:

This will help someone who is not familiar with the Oracle. The asnwer was very simple. I changed the column names to capital letters and this problem resolved. This must be an oracle thing.

tryCatch({
  ch=odbcConnect("<id>",pwd = "<passwd>")
  sql<-c("SELECT * FROM KEYNOTE_PRD WHERE KEYNOTE_PRD.SITE='High Frequency NY Traffic'")
  res<-sqlQuery(ch, sql)

},error = function(e) {
  print(odbcGetErrMsg(ch))

  print("retrive or connect to the db")
})
odbcClose(ch)

回答2:

I just ran into the same type of issue where the column names were lower and I had no control over the tables. The solution was to escape the quotes with single \ character. It worked like a charm.