[R] SQLDF syntax

Gabor Grothendieck ggrothendieck at gmail.com
Tue Apr 19 11:48:49 CEST 2011


On Mon, Apr 18, 2011 at 6:34 PM, new2R <bv_agrico at yahoo.co.in> wrote:
> Hi,
>
> I am new to R and trying to migrate from SAS. I am trying to use sqldf to
> create a new table from existed table and change some of the columns. I have
> table called DataOld with columns commodity, rate and total and I am trying
> to create new table called DataNew with columns commodity, ratenew and
> totalNew.
>
>> sqldf("create table datanew as select commodity, ratenew as rate * 10,
>> totalnew as total *10 from DataOld")
>
> I got error message " Error in sqliteExecStatement(con, statement,
> bind.data) :
>  RS-DBI driver: (error in statement: near "*": syntax error)
>

Its expression as name, not name as expression.  Try this:

> library(sqldf)
> BODnew <- sqldf("select demand, Time, demand + 1 as demandPlusOne from BOD")
> BODnew
  demand Time demandPlusOne
1    8.3    1           9.3
2   10.3    2          11.3
3   19.0    3          20.0
4   16.0    4          17.0
5   15.6    5          16.6
6   19.8    7          20.8

For more, the sqldf home page at http://sqldf.googlecode.com has links
to sqlite site where you can find sql syntax diagrams. See the links
along the left side of the page there.




-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com



More information about the R-help mailing list