Having trouble understanding how to pass a dataframe's column value to the query? The query works fine if I pass the value directly.
The values are stored in df$number. The tables names are changed for sharing purposes:
dataframe <- sqlQuery(connection, "
+ SELECT
+ dimension1,
+ FROM
+ table1,
+ WHERE
+ dimension1 = df$number ")
I get the error [1] "42S22 904 [Oracle][ODBC][Ora]ORA-00904: \"df$number\": invalid identifier\n"
The gsubfn package can perform quasi-perl-style string interpolation. Preface any function with fn$ to turn it on for that function's arguments:
library(gsubfn)
num <- 3
dataframe <- fn$sqlQuery(connection,
"SELECT dimension1 FROM table1 WHERE dimension1 = $num ")
Alternately use paste or sprintf to construct the string:
sql <- paste("SELECT dimension1 FROM table1 WHERE dimension1 =", num)
sqlQuery(connection, sql)
or
sql <- sprintf("SELECT dimension1 FROM table1 WHERE dimension1 = %d", num)
sqlQuery(connection, sql)
dataframe <- sqlQuery(connection, paste("
+ SELECT
+ dimension1,
+ FROM
+ table1,
+ WHERE
+ dimension1 =", df$number))
The SQL statement is a string, so it cannot access your dataframe's value. To construct a string which uses the value use:
sql.base<-"SELECT dimension1 FROM table1 WHERE dimension1 = "
sql.completed<-paste0(sql.base,df$number)
sqlQuery(connection, sql.completed)
If you're looking to do a parameterized SQL query i.e. you have a number of rows in df, you can either collapse the field values with a comma seperator and put them in side brackets within a SQL IN query. Alternatively you can use the package RODBCext which will allow to do it like
sqlExecute(connection, "SELECT dimension1 FROM table1 WHERE dimension1 = ?", df$number, fetch = TRUE)
For more info on RODBCext and this type of SQL, check out their vignette
