SQL> select ... where "R variable"

Before loading sql packages, i was wondering, once all packages required are
installed, if ?it's possible to do from R console sth like this:

SQL> select v1 from sql_table
? ? ? ?where v2 in ("R_variable") ?# being "R_variable" a vector

Try this:

# test data
R_variable <- data.frame(x = 1:10)
sql_table <- data.frame(v1 = 1:10, v2 = rep(0:1, each = 5))

library(sqldf)
sqldf("select v1 from sql_table where v2 in (select x from R_variable)")

The result of the last line is:
  v1
1  6
2  7
3  8
4  9
5 10

Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

Thank you, 

I guess it didn't work for me, maybe is not possible?

I've tried:

con<- odbcDriverConnect("Driver=SQL Server;
Server=...\\...;Database=...;Uid=...;Pwd=... ;")

v1=sqlQuery(con, "select v1 from sqltable where v3 =cte and v2 in (select
v2 from R_dataframe) order by (select v2 from R_dataframe)")

head(rbind(R_dataframe$v2, v1))

[,1]                                                                                               
       "1251"                                                                                             
v1 "42S02 208 [Microsoft][ODBC SQL Server Driver][SQL Server]*The name of
the object 'R_dataframe' is not valid.*"

Thanks in advance, 
user at host.com
   

--
View this message in context: http://r.789695.n4.nabble.com/SQL-select-where-R-variable-tp4190882p4194629.html
Sent from the R help mailing list archive at Nabble.com.

Thank you,

I guess it didn't work for me, maybe is not possible?

I've tried:

con<- odbcDriverConnect("Driver=SQL Server;
Server=...\\...;Database=...;Uid=...;Pwd=... ;")

v1=sqlQuery(con, "select v1 from sqltable where v3 =cte and v2 in (select
v2 from R_dataframe) order by (select v2 from R_dataframe)")

head(rbind(R_dataframe$v2, v1))

? ? ? [,1]
? ? ? "1251"
v1 "42S02 208 [Microsoft][ODBC SQL Server Driver][SQL Server]*The name of
the object 'R_dataframe' is not valid.*"

Since you have changed the problem by introducing new elements into it
clearly the answer must change too.  Either:

1. write R_variable to a table in your database and revise your SQL
statement so that its valid SQL or
2. if there are only a few elements in R_variable$x e.g. making it a
plain vector with R_variable <- 1:10 then construct the appropriate
sql statement:

R_variable <- 1:10
sql_stmt <- sprintf("select v1
   from sqltable
   where v2 in ( %s )
   order by v2
", toString(R_variable))

which gives:

cat(sql_stmt)

select v1
   from sql_table
   where v2 in ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 )
   order by v2

Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com