Re: [R] RODBC to query an Oracle table

From: Jim Porzak <jporzak_at_gmail.com>
Date: Mon, 28 Jul 2008 09:20:47 -0700

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.



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. Received on Mon 28 Jul 2008 - 16:34:01 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Mon 28 Jul 2008 - 17:32:42 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.

list of date sections of archive