CODEWITHSUNDEEP
sqlrdbi
Amit
Amit

Reputation: 793

how to use select statement with dbGetQuery in Rstudio?

I have set a connection from R to the oracle database but not able to rectify this error

library(RJDBC)
library(DBI)
library(odbc)
con <- dbConnect(odbc(), "zzz", UID="xyz" , PWD ="xyz" )
z= dbGetQuery(con,"select * from car_price_table where type= 'car' and Date %between% c('2010-01-01', Sys.date()) ")

But I am getting this error

Error: nanodbc/nanodbc.cpp:1655: HY000: [Oracle][ODBC][Ora]ORA-00911: invalid character

Upvotes: 0

Views: 930

Answers (1)

Waldi
Waldi

Reputation: 41220

You can't directly mix R syntax with SQL syntax. In particular %between% isn't a valid SQL statement, hence the invalid character error.

You could use glue or paste0 to insert R values in SQL code :

qry <- glue::glue("
SELECT * FROM car_price_table
WHERE type= 'car' 
AND Date BETWEEN '2010-01-01' AND '{Sys.Date()}'")

qry
SELECT * FROM car_price_table
WHERE type= 'car' 
AND Date BETWEEN '2010-01-01' AND '2021-03-22'
                 
result = dbGetQuery(con,qry)

Also using Date as field name might be risky because DATE is a reserved keyword.

Upvotes: 4

Related Questions