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?
--
View this message in context:
http://n4.nabble.com/question-on-sqldf-syntax-tp1289707p1289707.html
Sent from the R help mailing list archive at Nabble.com.
Actually, better sql would likely be:
dbs.final <- sqldf("select * from dbs.possible.combos left join
dbs.aggregate using (Date,Place)")
but this still doesn't work
--
View this message in context:
http://n4.nabble.com/question-on-sqldf-syntax-tp1289707p1289718.html
Sent from the R help mailing list archive at Nabble.com.
On Mon, Jan 25, 2010 at 2:17 PM, GL <pflugg at shands.ufl.edu> wrote:> > 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?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:> dbs.finalDate 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
Maybe that's a "problem" with the RSQLite package , probably
detaching
the package help.
detach("package:RSQLite")
HTH, Christian
> 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?
>
>
>
>
>