Dear alls, Could someone tell me how to select a subset of string observations (e.g. "females" in a sex column) with sqlQuery in the RODBC library? Indeed, I'm trying to select a subset of observations on my access database with: female<-sqlQuery(mychannel,"SELECT Micromammiferes.sex FROM Micromammiferes WHERE (((Micromammiferes.sex)="females"));") The sql works well in access but in R, I keep getting: Error: syntax error. Any help would be very appreciated, Thanks a lot J??r??me Lema??tre Ph.D. student D??partment of biology, University Laval Quebec, Canada
J??r??me Lema??tre wrote:> Dear alls, > > Could someone tell me how to select a subset of string observations (e.g. > "females" in a sex column) with sqlQuery in the RODBC library? > > Indeed, I'm trying to select a subset of observations on my access database > with: > > female<-sqlQuery(mychannel,"SELECT Micromammiferes.sex > FROM Micromammiferes > WHERE (((Micromammiferes.sex)="females"));") > > The sql works well in access but in R, I keep getting: > > Error: syntax error.R is likely to have problems with nested quote characters. Ie: x = "SELECT Micromammiferes.sex FROM Micromammiferes WHERE (((Micromammiferes.sex)="females"));" also results in a syntax error (my mailer split the line).
(sorry if a duplicate pops through ...) J??r??me Lema??tre wrote:> Dear alls, > > Could someone tell me how to select a subset of string observations (e.g. > "females" in a sex column) with sqlQuery in the RODBC library? > > Indeed, I'm trying to select a subset of observations on my access database > with: > > female<-sqlQuery(mychannel,"SELECT Micromammiferes.sex > FROM Micromammiferes > WHERE (((Micromammiferes.sex)="females"));") > > The sql works well in access but in R, I keep getting: > > Error: syntax error.Most computer software has problems with nested quote characters. Ie: > x = "SELECT Micromammiferes.sex FROM Micromammiferes WHERE (((Micromammiferes.sex)="females"));" also results in a syntax error (my mailer split the line).
The syntax error is that you have unescaped quotes inside quotes. You also do not need a semicolon, nor to refer to columns in this table.column form. Try 'select sex from Micromammiferes where sex="females"' (I suspect you do not need quotes, but keep forgetting the quirks of various DBMSs.) On Wed, 19 Oct 2005, J?r?me Lema?tre wrote:> Dear alls, > > Could someone tell me how to select a subset of string observations (e.g. > "females" in a sex column) with sqlQuery in the RODBC library? > > Indeed, I'm trying to select a subset of observations on my access database > with: > > female<-sqlQuery(mychannel,"SELECT Micromammiferes.sex > FROM Micromammiferes > WHERE (((Micromammiferes.sex)="females"));") > > The sql works well in access but in R, I keep getting: > > Error: syntax error. > > Any help would be very appreciated, > > Thanks a lot > > J?r?me Lema?tre > Ph.D. student > D?partment of biology, > University Laval > Quebec, Canada > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html >-- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595