sql keyword IN using RSQLite and DBI package

Question

When using the RSQLite and DBI package to form a query, I wonder if there is a way to use SQL keyword IN?

I don't think SQL keyword IN is currently implemented?

e.g.

## instead of:
dbGetQuery(con, "SELECT * FROM mtcars WHERE cyl = :targetCyl" , 
params = list( targetCyl = 4 ))

## I want to do the following:

dbGetQuery(con, "SELECT * FROM mtcars WHERE cyl IN :targetCyl" , 
params = list( targetCyl = c( 2, 4, 6 ) ))

r2evans · Accepted Answer · 2022-08-06 11:36:17Z

1

SQL's IN operator requires its arguments to be in parentheses, as in
```
select * from mtcars where cyl in (?,?,?)
```

DBI requires you to instantiate the number of parens you need, as in

targetCyl <- c( 2, 4, 6 )
dbGetQuery(
  con, paste("SELECT * FROM mtcars WHERE cyl IN (",
             paste(rep("?", length(targetCyl)), collapse = ","), ")"),
  params = as.list(targetCyl))

FYI, this is not specific to SQLite or RSQLite, it's DBI in general.

edited Aug 6, 2022 at 11:36

answered Jul 23, 2022 at 10:54

r2evans

167k8 gold badges92 silver badges175 bronze badges

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

Collectives™ on Stack Overflow

sql keyword IN using RSQLite and DBI package

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related