[R] RE : sqlQuery and string selection

From: Jérôme Lemaître <je_lemaitre_at_hotmail.com>
Date: Thu 20 Oct 2005 - 10:58:25 EST


Dear Prof Ripley,

I tried what you suggested and it worked perfectly well! I also appreciated your corrections about my SQL formulation. However, it seems that quotes are indeed needed to get string observations (but my access software is in french...).
Finally, do you think that it might be worth to add an example for sqlQuery using string variables in a later version of RODBC, just in case others encounter the same problem as me?

I thank you very much for your help,

Jérôme Lemaître
Ph.D. student
Départment of biology,
University Laval
Quebec, Canada

-----Message d'origine-----
De : Prof Brian Ripley [mailto:ripley@stats.ox.ac.uk] Envoyé : 19 octobre 2005 18:23
À : Jérôme Lemaître
Cc : R-help@stat.math.ethz.ch
Objet : Re: [R] sqlQuery and string selection

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@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@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

______________________________________________
R-help@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
Received on Thu Oct 20 11:03:17 2005

This archive was generated by hypermail 2.1.8 : Fri 03 Mar 2006 - 03:40:46 EST