SQLDF error - syntax error

Question

I'm running the following command:

sqldf("SELECT * FROM dat WHERE Form LIKE '%compare%' AND Create.Date LIKE '%7/01/11%' AND Post.Status = 'Success'")

but end up getting the following error.

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

Can anyone tell me what's up.

> str(dat)
'data.frame':   23 obs. of  12 variables:
 $ ID            : int  4873 4874 4890 4893 4895 4902 4904 4905 4906 4921 ...
 $ Name          : Factor w/ 18 levels "Casey Ryan","Elizabeth Cullen",..: 13 9 15 15 16 15 15 15 16 12 ...
 $ Create.Date   : Factor w/ 23 levels "03:23.0","14:43.0",..: 3 15 20 13 23 6 12 14 21 4 ...
 $ In.Click.ID   : int  32787 32788 13480 21050 21041 32824 27807 27806 15350 32911 ...
 $ Traffic.Source: Factor w/ 3 levels "FACEBOOK","GOOGLE",..: 3 3 1 3 2 3 3 3 2 3 ...
 $ Refer.Domain  : Factor w/ 7 levels "","autoinsurancestep",..: 3 2 7 1 2 4 4 2 5 4 ...
 $ Form          : Factor w/ 3 levels "","autoinsurancestep",..: 2 1 1 1 3 3 3 3 1 3 ...
 $ Keyword       : Factor w/ 3 levels "","{keyword}",..: 1 1 2 1 3 1 1 1 2 1 ...
 $ Ping.Status   : Factor w/ 2 levels "","Success": 1 2 1 1 1 2 1 1 1 2 ...
 $ Post.Status   : Factor w/ 2 levels "","Success": 1 2 1 1 1 2 1 1 1 2 ...
 $ Buyer         : Factor w/ 8 levels "","ALL_WEB_LEADS",..: 1 6 1 1 1 7 1 1 1 8 ...
 $ Amount        : int  0 217 0 0 0 3000 0 0 0 1200 ...

It would help if you provided some info on the structure of dat, perhaps using dput(head(dat)). — joran
– joran, Commented Sep 14, 2011 at 20:40
Just scanning the examples in ?sqldf it seems that periods in variable names are trouble. Note the examples using the iris data set. Try using underscores instead, or renaming the columns entirely. — joran
– joran, Commented Sep 14, 2011 at 20:51
Separately, I don't think you are going to match anything with the Create.Date (even if you rename to get around the dot problem) because in your dat the variable is "03:23.0" and you are trying to match on %7/01/11% (although I could be wrong and one of the other 21 levels might match that). — Brian Diggs
– Brian Diggs, Commented Sep 14, 2011 at 21:04
Dots in column names and SQL are a big no no. See FAQ question 2 @ code.google.com/p/sqldf — Roman Luštrik
– Roman Luštrik, Commented Sep 15, 2011 at 0:52
While dots are not allowed in proper SQL, you can substitute an underscore for a dot in column names and surround table names that include dots with backticks for the purposes of sqldf. — Jeff Erickson
– Jeff Erickson, Commented Sep 16, 2012 at 14:48

MvG · Accepted Answer · 2012-08-01 08:49:10Z

1

In SQL, a dot separates database name from table name. So don't use dots in names for this.

answered Aug 1, 2012 at 8:49

MvG

61.8k25 gold badges158 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLDF error - syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related