sqldf can't read logical vectors in R

Question

I am trying to use sqldf with a dataframe created in R. My table has 2 columns of logical vectors. My code looks like sqldf("Select* from df where Dupe1='TRUE' or Dupe2='TRUE' '' ). It returns the Error: unexpected numeric constant. Do not understand what this means. I assume that sql can't read R's logical vectors. Any solution would be greatly appreciated.

Answer 1

It may just how things were copied, but in your question, your sql statement ends with two single quotes, not a double quote. But the big problem here is that TRUE and FALSE are not stored as strings. TRUE is stored as 1. FALSE is stored as 0. So the code below does what you want. I added a row number so that we can see which rows were selected.

library(sqldf)

## Create test data
RowNum = 1:20
Dupe1 = runif(20) > 0.5
Dupe2 = runif(20) > 0.5
df = data.frame(RowNum, Dupe1, Dupe2)

sqldf("Select * from df where Dupe1=1 OR Dupe2=1")

   RowNum Dupe1 Dupe2
1       3  TRUE  TRUE
2       5  TRUE  TRUE
3       6 FALSE  TRUE
4       9  TRUE  TRUE
5      10 FALSE  TRUE
6      13 FALSE  TRUE
7      14  TRUE  TRUE
8      15 FALSE  TRUE
9      17 FALSE  TRUE
10     18 FALSE  TRUE
11     20 FALSE  TRUE