How to handle @Param in a SQL IN clause

[joranE]

Many times we have SQL like:

select 
  col1,
  col2
from
  my_table
where
  col3 in @param

I tried:

> sql %>% sq_set(param = '(1,2,3)')
select 
  col1,
  col2
from
  my_table
where
  col3 in '(1,2,3)'

and,

sql %>% sq_set(param = 1:3)
select 
  col1,
  col2
from
  my_table
where
  col3 in 1
Warning message:
In gsub(paste0(prefix, pattern), value, query, perl = TRUE) :
  argument 'replacement' has length > 1 and only the first element will be used

and,

> sql %>% sq_set(param = "1,2,3")
select 
  col1,
  col2
from
  my_table
where
  col3 in '1,2,3'

None result in valid SQL.

There should be a way to do this, probably.

[smbache]

R lists will be converted to SQL lists, so:

sql %>% sq_set(param = list(1,2,3))

should work, or if you have a vector, vec:

sql %>% sq_set(param = as.list(vec))

[joranE]

Ah, ok. Thanks, IN clauses are always the first thing I try with these sorts of SQL tools.

[smbache]

Glad I have that one covered, then :) I guess I should document this in the README!

[ThoDuyNguyen]

This is exactly my problem.

Thank you for pointing out.

<3

[BriBecker]

The IN clause is always something I worry about first. Great!!