R programming: RODBC and dataframe

Question

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"

Answer 1

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)