Hello all, I am having trouble running a count function in R using RODBC to query a table I created in Oracle. It may very well be that my SQL coding is incorrect; I just started learning it. But if someone could point me in the right direction or tell me if I am going about this the correct way that would be greatly appreciated! The script I have right now is:>require(RODBC) >channel<-odbcConnect("MeganXE") >stbl<-sqlQuery(channel, 'select plotnum, sampyear, sptype from density') >sqlQuery(channel, 'select count (sampyear, plotnum, sptype) from stbl where sampyear=1995 AND plotnum=1 AND sptype='S' OR sptype='H')I get this error: unexpected symbol in "sqlQuery(channel...)" I would like R to return a count of how many records satisfy the condition of being sampled in 1995 and in plot number 1, where the species type is either softwood or hardwood (S or H). Any help with this would be great. Thanks in advance, Megan
on 07/28/2008 09:44 AM Megan J Bellamy wrote:> Hello all, > > I am having trouble running a count function in R using RODBC to > query a table I created in Oracle. It may very well be that my SQL > coding is incorrect; I just started learning it. But if someone could > point me in the right direction or tell me if I am going about this > the correct way that would be greatly appreciated! The script I have > right now is: > >> require(RODBC) channel<-odbcConnect("MeganXE") >> stbl<-sqlQuery(channel, 'select plotnum, sampyear, sptype from >> density') sqlQuery(channel, 'select count (sampyear, plotnum, >> sptype) from stbl where sampyear=1995 AND plotnum=1 AND sptype='S' >> OR sptype='H') > > I get this error: unexpected symbol in "sqlQuery(channel...)" > > I would like R to return a count of how many records satisfy the > condition of being sampled in 1995 and in plot number 1, where the > species type is either softwood or hardwood (S or H). Any help with > this would be great. > > Thanks in advance, > > Megan'stbl' is a data frame in R, not a table in Oracle. Thus, when you try to run the subsequent query against 'stbl', you are getting an error message indicating that 'stbl' cannot be found in Oracle. You can do the following in R: nrow(subset(stbl, (sampyear == 1995) & (plotnum == 1) & (sptype == "S" | sptype == "H"))) See ?subset HTH, Marc Schwartz
It's your R syntax that is faulty: Use "" (not ') to delimit the R string which contains single quotes for use by SQL. On Mon, 28 Jul 2008, Megan J Bellamy wrote:> Hello all, > > I am having trouble running a count function in R using RODBC to query a table I created in Oracle. It may very well be that my SQL coding is incorrect; I just started learning it. But if someone could point me in the right direction or tell me if I am going about this the correct way that would be greatly appreciated! The script I have right now is: > >> require(RODBC) >> channel<-odbcConnect("MeganXE") >> stbl<-sqlQuery(channel, 'select plotnum, sampyear, sptype from density') >> sqlQuery(channel, 'select count (sampyear, plotnum, sptype) from stbl where sampyear=1995 AND plotnum=1 AND sptype='S' OR sptype='H') > > I get this error: unexpected symbol in "sqlQuery(channel...)" > > I would like R to return a count of how many records satisfy the condition of being sampled in 1995 and in plot number 1, where the species type is either softwood or hardwood (S or H). Any help with this would be great. > > Thanks in advance, > > Megan > > ______________________________________________ > 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. >-- 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
Hi Megan,
Marc's hint is the R way, which is needed with your original SELECT.
OTOH, it may be more efficient to move the WHERE clause into the
SELECT. Something like
select plotnum, sampyear, sptype
from density
where sampyear=1995
AND plotnum=1
AND sptype IN ('S', 'H')
should work if I understand your intent.
Then you have a smaller data frame on which to work in R.
BTW, I always write & test my SQL using a database tool such as
DvVisualizer http://www.dbvis.com/, my current favorite.
That way you can develop (& debug) you SELECT's in a SQL aware
environment before using them in R. If the query is at all
non-trivial, I tend to save it in text form from DbViz and point to it
in R with something like:
query <- readChar(paste(path2sql, "MySelect.sql", sep =
""), nchars = 999999)
Usage <- dbGetQuery(conn, query)
HTH,
Jim Porzak
Responsys, Inc.
San Francisco, CA
http://www.linkedin.com/in/jimporzak
On Mon, Jul 28, 2008 at 7:44 AM, Megan J Bellamy <bellammj at gov.ns.ca>
wrote:>
> Hello all,
>
> I am having trouble running a count function in R using RODBC to query a
table I created in Oracle. It may very well be that my SQL coding is incorrect;
I just started learning it. But if someone could point me in the right direction
or tell me if I am going about this the correct way that would be greatly
appreciated! The script I have right now is:
>
> >require(RODBC)
> >channel<-odbcConnect("MeganXE")
> >stbl<-sqlQuery(channel, 'select plotnum, sampyear, sptype from
density')
> >sqlQuery(channel, 'select count (sampyear, plotnum, sptype) from
stbl where sampyear=1995 AND plotnum=1 AND sptype='S' OR
sptype='H')
>
> I get this error: unexpected symbol in "sqlQuery(channel...)"
>
> I would like R to return a count of how many records satisfy the condition
of being sampled in 1995 and in plot number 1, where the species type is either
softwood or hardwood (S or H). Any help with this would be great.
>
> Thanks in advance,
>
> Megan
>
> ______________________________________________
> 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.
On Mon, 28 Jul 2008, Megan J Bellamy wrote:> Hello all, > > I am having trouble running a count function in R using RODBC to query a table I created in Oracle. It may very well be that my SQL coding is incorrect; I just started learning it. But if someone could point me in the right direction or tell me if I am going about this the correct way that would be greatly appreciated! The script I have right now is: > >> require(RODBC) channel<-odbcConnect("MeganXE") stbl<-sqlQuery(channel, >> 'select plotnum, sampyear, sptype from density') sqlQuery(channel, >> 'select count (sampyear, plotnum, sptype) from stbl where sampyear=1995 >> AND plotnum=1 AND sptype='S' OR sptype='H') > > I get this error: unexpected symbol in "sqlQuery(channel...)" > > I would like R to return a count of how many records satisfy the > condition of being sampled in 1995 and in plot number 1, where the > species type is either softwood or hardwood (S or H). Any help with this > would be great. >Well that error message I am almost certain is saying your SQL code is wrong and it is pretty easy to see that the quote marks will cause you problems. Try using double quotes in the SQL code ("S", "H") and don't forget to close the single quote which starts at 'select. (That is you need a ' just before the closing bracket. David scott _________________________________________________________________ David Scott Department of Statistics, Tamaki Campus The University of Auckland, PB 92019 Auckland 1142, NEW ZEALAND Phone: +64 9 373 7599 ext 86830 Fax: +64 9 373 7000 Email: d.scott at auckland.ac.nz Graduate Officer, Department of Statistics Director of Consulting, Department of Statistics