2

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 ...
rcs
  • 67,191
  • 22
  • 172
  • 153
ATMathew
  • 12,566
  • 26
  • 69
  • 76
  • 2
    It would help if you provided some info on the structure of `dat`, perhaps using `dput(head(dat))`. – joran Sep 14 '11 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 Sep 14 '11 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 Sep 14 '11 at 21:04
  • 2
    Dots in column names and SQL are a big no no. See FAQ question 2 @ http://code.google.com/p/sqldf/ – Roman Luštrik Sep 15 '11 at 00: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 Sep 16 '12 at 14:48

1 Answers1

1

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

MvG
  • 57,380
  • 22
  • 148
  • 276