Start by using single quotes in your paste() command.
dumb example:
sql <- paste(' select x = "3" ')
(the query is nonsense, I just wrote it to show
how to get double quotes into the query)
-Don
At 6:45 PM +0200 9/21/09, BOISSON, Pascal wrote:
>Dear R users,
>
>I am trying to connect R to data that is in a
>Access Database but I have problem with the
>construction of queries using special characters.
>
>I am using RODBC package.
>
>The following is working :
>
>> MyQuery<-paste("SELECT first( (DateHeure) ) ,
>>avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3
>>")
>> Col3<-sqlQuery(con, query=MyQuery)
>> Col3
> Expr1000 Cond
>1 2009-06-23 10:15:02 579.9562
>>
>> MyQuery<-paste("SELECT first( format
>>(DateHeure) ) , avg(NNO3_AT322_OUT_moy) AS Cond
>>FROM Colonne_3 ")
>> Col3<-sqlQuery(con, query=MyQuery)
>> Col3
> Expr1000 Cond
>1 23/06/2009 10:15:02 579.9562
>
>But I have problems as soon as I want to use SQL
>functions in my query that use a double quote
>(eg FORMAT() function). I have no idea on how to
>build my sql instruction containing "" since the
>escape code \" does not seem to work/be
>sufficient in this case. (Nota : the argument I
>would like to use in the following call to
>format is "00" )
>
>#Obvious error :
>> MyQuery<-paste("SELECT first(
>>format(DateHeure, "00")) ) ,
>>avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3
>>")
>Erreur : constante numérique inattendu(e) dans
>"MyQuery<-paste("SELECT first( format(DateHeure,
>"00"
> Expr1000 Cond
>1 23/06/2009 10:15:02 492.0594
>>
>
>#Trying to solve the error using the escape code \" :
>> MyQuery<-paste("SELECT first(
>>format(DateHeure, \"00\")) ) ,
>>avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3
>>")
>> Col3<-sqlQuery(con, query=cat(MyQuery))
>SELECT first( format(DateHeure, "00")) ) ,
>avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3
>Erreur dans odbcQuery(channel, query,
>rows_at_time) :
> 'getEncChar' doit être appelé sur un CHARSXP
>
>Do you have any idea on how to proceed?
>
>With Best regards
>Pascal Boisson
>
>___________________________________________________________________________________
>
>Protegeons ensemble l'environnement : avez-vous
>besoin d'imprimer ce courrier electronique ?
>___________________________________________________________________________________
>
>Les informations figurant sur cet e-mail ont un
>caractere strictement confidentiel et sont
>exclusivement adressees au destinataire
>mentionne ci-dessus.Tout usage, reproduction ou
>divulgation de cet e-mail est strictement
>interdit si vous n'en etes pas le destinataire.
>Dans ce cas, veuillez nous en avertir
>immediatement par la meme voie et detruire
>l'original. Merci.
>
>This e-mail is intended only for use of the
>individual or entity to which it is addressed
>and may contain information that is privileged,
>confidential and exempt from disclosure under
>applicable law.
>Any use, distribution or copying of this e-mail
>communication is strictly prohibited if you are
>not the addressee. If so, please notify us
>immediately by e-mail, and destroy the original.
>Thank you.
>
>______________________________________________
>R-help at 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.
--
--------------------------------------
Don MacQueen
Environmental Protection Department
Lawrence Livermore National Laboratory
Livermore, CA, USA
925-423-1062