Can't figure out why syntax error near "from" is appearing for sqldf in R

Question

I'm trying to compare subject IDs between columns to find unique IDs. I keep on getting a syntax error even though I've spent the past hour checking and re-checking my syntax. I decided to make a simple data frame to play around with an low and behold I get the same error.

Here my syntax for the proxy data frame

color <- c('yellow', 'red', 'green', 'blue') 
number <- c(1,3,4,5) 
stuff <- data.frame(color, number) 

sqldf('select number, from stuff where color = red')

Here's the error I got

Error in sqliteSendQuery(con, statement, bind.data) : error in statement: near "from": syntax error

I'm beyond frustrated that I can't get this simple query to work. What gives? I even tried removing the comma before 'from' and then I get the following error.

Error in sqliteSendQuery(con, statement, bind.data) : error in statement: no such column: red

tried removing that and I got the second error about no column called red existing, which is true. but that shouldn't matter since i explicitly stated I wanted it to look in the column 'color' — hollo056
– hollo056, Commented Mar 24, 2016 at 17:28
surround either side of red with quotes. I did not see that before. 'red' — Vamsi Prabhala
– Vamsi Prabhala, Commented Mar 24, 2016 at 17:29
You didn't put red in quotes. Use double quotes for the query, single quotes for things in the query that need to be quoted. — joran
– joran, Commented Mar 24, 2016 at 17:29
Since it's clear you don't understand SQL that well, why don't you use base R subsetting and whatnot? — cory
– cory, Commented Mar 24, 2016 at 17:32

fishtank · Accepted Answer · 2016-03-24 17:34:06Z

3

Remove commas and change quotes:

> stuff
   color number
1 yellow      1
2    red      3
3  green      4
4   blue      5

> sqldf("select number from stuff where color = 'red'")
  number
1      3

answered Mar 24, 2016 at 17:34

fishtank

3,7281 gold badge16 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Can't figure out why syntax error near "from" is appearing for sqldf in R

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