CODEWITHSUNDEEP
rrodbcrjdbc
Blake S.
Blake S.

Reputation: 411

R programming: RODBC and dataframe

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"

Upvotes: 1

Views: 1250

Answers (3)

Steph Locke
Steph Locke

Reputation: 6146

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

Upvotes: 0

tmpname12345
tmpname12345

Reputation: 2921

dataframe <- sqlQuery(connection, paste("
+ SELECT
+ dimension1,
+ FROM
+ table1,
+ WHERE
+ dimension1 =",  df$number))

Upvotes: 0

G. Grothendieck
G. Grothendieck

Reputation: 269441

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)

Upvotes: 4

Related Questions