0

First table DailyOil with the fields DayofMonth, Month, Year, EXPL, AB,…

Second Table HPower with the fields HourEnding, Day, Month, Year, MWh1, MWh2, ….

I want to create a new table with HourEnding, Day, Month, Year, MWh1, MWh2, EXPL, AB

Notice the second table as an addition time field so is 24 times long than the oil table.

The R code:

Library(sqldf)
df4 <- sqldf("SELECT HP.Month, HP.Day, HP.Year, HP.Express_Avg, HP.Platte_Avg, HP.Full_Avg, HP.CasperToGurley_Avg, HP.OgallallatoEthlyn_Avg, OD.EXPL, OD.PLATTE, OD.CASPERtoGUERNSEY 
              FROM HPower HP 
              LEFT JOIN DailyOil OD 
              on HP.Day = OD.DayofMonth and HP.Month = OD.Month and HP.Year = OD.Year")

Error in sqliteExecStatement(con, statement, bind.data) : 
  RS-DBI driver: (error in statement: near "FROM": syntax error)
joran
  • 169,992
  • 32
  • 429
  • 468
James Crosbie
  • 13
  • 2
  • 8
  • Just a guess: what happens if you explicitly say `HPower AS HP`, and similarly for the other table? – joran Oct 29 '13 at 19:57
  • I still get the same error with: df4 <- sqldf("SELECT HP.Month, HP.Day, HP.Year, HP.Express_Avg, HP.Platte_Avg, HP.Full_Avg, HP.CasperToGurley_Avg, HP.OgallallatoEthlyn_Avg, OD.EXPL, OD.PLATTE, OD.CASPERtoGUERNSEY + FROM HPower as HP LEFT JOIN DailyOil as OD + on HP.Day = OD.DayofMonth and HP.Month = OD.Month and HP.Year = OD.Year") – James Crosbie Oct 29 '13 at 20:19
  • sqldf uses sqlite and the [syntax for `SELECT`](http://www.sqlite.org/lang_select.html) does use `AS`. Your second attempt in the comment looks better (apart from having stray `+` symbols). Did you get the same error message? – seancarmody Oct 29 '13 at 20:28
  • MAke the code in your question reproducible. See this: http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example (Also sqlite does not require AS.) – G. Grothendieck Oct 29 '13 at 22:19

0 Answers0