Skip to content

question on sqldf syntax

5 messages · GL, Leon, Gabor Grothendieck +1 more

GL
# 
trying to structure sql to merge two datasets. structure follows:

dbs.possible.combos (all possible combinations of dates and places)
Date Place
1/1/10 N-01
1/1/10 S-02
1/2/10 N-01
1/2/10 S-02
etc...

dbs.aggregate (the raw data aggregated by date and location)
Date Place Days
1/1/10 N-01 6
1/1/10 S-02 10
1/2/10 S-02 5


Trying to merge so I look-up the values for each possible combo 
dbs.final <- sqldf("select dbs.possible.combos$Date,
dbs.possible.combos$Place, dbs.possible.combos$Days FROM dbs.possible.combos
LEFT JOIN dbs.aggregate ON (dbs.possible.combos$Place = dbs.aggregate$Place)
AND (dbs.possible.combos$Date = dbs.aggregate$Date)")

Resulting in: 
Error in sqliteExecStatement(con, statement, bind.data) : 
  RS-DBI driver: (error in statement: near ".": syntax error)

What am I getting wrong in the syntax?
Leon
#
On 25 Jan., 20:26, GL <pfl... at shands.ufl.edu> wrote:
I'd suspect name mangling to cause the problem:
http://code.google.com/p/sqldf/#2._Why_does_sqldf_seem_to_mangle_certain_variable_names?

Try to quote the table names with backticks (`).
Gabor Grothendieck
#
On Mon, Jan 25, 2010 at 2:17 PM, GL <pflugg at shands.ufl.edu> wrote:
You have to pass it a valid SQL statement but $ is not an SQL
operator.  Also dot (.) is an SQL operator so you have quote
identifiers that contain a dot so that it will not regard those dots
as operators.

Try this:

library(sqldf)

lines1 <- "Date Place
1/1/10 N-01
1/1/10 S-02
1/2/10 N-01
1/2/10 S-02"
dbs.possible.combos <-
   read.table(textConnection(lines1), header = TRUE, as.is = TRUE)

lines2 <- "Date Place Days
1/1/10 N-01 6
1/1/10 S-02 10
1/2/10 S-02 5"
dbs.aggregate <- read.table(textConnection(lines2), header = TRUE, as.is = TRUE)

dbs.final <- sqldf('select Date, Place, Days
	FROM "dbs.possible.combos"
	LEFT JOIN "dbs.aggregate" using (Place, Date)')

Giving:
Date Place Days
1 1/1/10  N-01    6
2 1/1/10  S-02   10
3 1/2/10  N-01   NA
4 1/2/10  S-02    5
Christian Schulz
# 
Maybe that's a "problem" with the  RSQLite package , probably detaching 
the package help.
detach("package:RSQLite")

HTH, Christian