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?
thank you,
-david
library(RJDBC)
drv <- JDBC("com.mysql.jdbc.Driver",
"/home/davideps/Software/extlibs/mysql-connector-java-5.0.7-bin.jar",identifier.quote="`")
conn <- dbConnect(drv, "jdbc:mysql://localhost/civicrm",
"userid","pass")
org_table=dbGetQuery(conn,"SELECT id,organization_name FROM
civicrm_contact WHERE contact_type=?","organization")
dbGetQuery(conn,"SELECT id from civicrm_relationship WHERE contact_id_a
IN ? AND contact_id_b IN ?",org_table$id,org_table$id)
You are misusing SQL variable substitution. Just embed the list in the SQL
statement itself.
paste("SELECT * FROM this_table WHERE this_column IN (",
paste(org_table$id, collapse=TRUE),")",sep="")
Better yet, use a SQL join so you can do this sequence in one SQL statement.
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<jdnewmil@dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
---------------------------------------------------------------------------
Sent from my phone. Please excuse my brevity.
David Epstein <davideps@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?
thank you,
-david
library(RJDBC)
drv <- JDBC("com.mysql.jdbc.Driver",
"/home/davideps/Software/extlibs/mysql-connector-java-5.0.7-bin.jar",identifier.quote="`")
conn <- dbConnect(drv, "jdbc:mysql://localhost/civicrm",
"userid","pass")
org_table=dbGetQuery(conn,"SELECT id,organization_name FROM
civicrm_contact WHERE contact_type=?","organization")
dbGetQuery(conn,"SELECT id from civicrm_relationship WHERE contact_id_a
IN ? AND contact_id_b IN ?",org_table$id,org_table$id)
_____________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
[[alternative HTML version deleted]]
thank you! I was able to get it to work with collapse="," On Wed, 2011-10-19 at 01:52 -0500, Jeff Newmiller wrote:> paste("SELECT * FROM this_table WHERE this_column IN (", > paste(org_table$id, collapse=TRUE),")",sep="")
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
Gabor, thank you. I just got the script working using paste but your solution looks good as well. I was not familiar with gsubfn. Appears very useful. -david> 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. >