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