SQLDF syntax
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