[R] SQL> select ... where "R variable"

Gabor Grothendieck ggrothendieck at gmail.com
Wed Dec 14 21:15:45 CET 2011


On Wed, Dec 14, 2011 at 7:04 AM, agent dunham <crosspide at hotmail.com> wrote:
> 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



More information about the R-help mailing list