[R] Using SQL "IN" with RJDBC or RMySQL

[Gabor Grothendieck]

On Wed, Oct 19, 2011 at 12:35 AM, David Epstein <davideps at umich.edu> wrote:
> Hello,
>
>
> The code below works fine up until I try to use the "IN" statement in
> the last line. The proper SQL format is:
>
> SELECT * FROM this_table WHERE this_column IN (1,2,3,4,5)
>
> But, I think I may be getting something like:
>
> SELECT * FROM this_table WHERE this_column IN c(1,2,3,4,5)
>
> Which makes no sense in SQL. I think it may be just a matter of string
> massaging, but I'm not certain. I've tried to build the query using
> sprintf statements with no luck. I could not get RMySQL to do it either.
> Any suggestions?
>

With gsubfn if  you preface your function with fn$ as shown below then
it turns on a quasi-perl style string interpolation:

library(gsubfn)
organization <- 3
org_table <- fn$dbGetQuery(conn,""SELECT id,organization_name FROM
civicrm_contact WHERE contact_type=$organization")

See ?fn and the gsubfn home page (http://gsubfn.googlecode.com) for details.

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