[R] RE : sqlQuery and string selection

[Jérôme Lemaître]

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 at stats.ox.ac.uk] 
Envoyé : 19 octobre 2005 18:23
À : Jérôme Lemaître
Cc : R-help at 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 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