Re: [R] Variables in RODBC environment

From: Bonfigli Sandro <bonfigli_at_inmi.it>
Date: Thu 05 Oct 2006 - 10:14:56 GMT

> how can I use variables in the RODBC environment.
>
> Example which does not work:
>
> Thanks for your help.
>
> Thorsten
>
> pn <- '39R5238';
>
> library(RODBC);
> odbcobj <- odbcConnect("SQUIT21C",uid="muehge",pwd="xxx");
> sql <- "select
> u.unitid,
> from test
> where part in ('pn')
> ";
> parameter <- sqlQuery(odbcobj,sql);
> odbcClose(odbcobj);
>

You can "compose" your query simply using paste.

In your example it would be

pn <- '39R5238';

sql <- paste("select
u.unitid,
from test
where part in ('",
pn,
"')");

or, to avoid problems with the newline character:

sql <- paste("select",

"u.unitid,",
"from test",
"where part in ('", 

pn,
"')");

In this case you'd have:
> sql

[1] "select u.unitid, from test where part in (' 39R5238 ')"

It's clear that, if the spaces in (' 39R5238 ') are a problem for you , you can use

sql <- paste("select ",

"u.unitid, ",
"from test ",
"where part in ('", 

pn,
"')", sep = "");

which lends to

> sql

[1] "select u.unitid, from test where part in ('39R5238')"

HTH   Sandro Bonfigli



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 and provide commented, minimal, self-contained, reproducible code. Received on Thu Oct 05 20:07:05 2006

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.1.8, at Thu 05 Oct 2006 - 10:30:08 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.