CODEWITHSUNDEEP
sqlrr-markdown
Henry
Henry

Reputation: 28

Reference a of list values in R in a sql chunk

I want to reference a list of values to be used in a sql chunk.

I have tried using the dplyr::translate_sql function to convert the list to a correct syntax, but it doesn't seem to be correct. I can copy and paste the output of the translate_sql object and it will run, but I cannot embed thoe object.

values <- translate_sql(c("value1", "value2", "value3"))

print(values)

<SQL> ('value1', 'value2', 'value3')

select *
from db.table
where column in ?values

I get an error message saying the syntax is incorrect.

However if I just copy and paste the values (See code below, it runs perfectly)

select *
from db.table
where column in ('value1', 'value2', 'value3')

Upvotes: 1

Views: 1810

Answers (3)

magster
magster

Reputation: 21

After much pain, this blog solved this problem for me: https://irene.rbind.io/post/using-sql-in-rstudio/#passing-variables-tofrom-sql-chunks "Adding an * to the end of the variable collapses the vector into a single comma-separated expression, rather than outputting a vector of SQL expressions."

values<-glue::glue_sql("{myrvariable*}", .con = con)
SELECT * FROM yourtable WHERE column IN (?values)

Upvotes: 2

Yifu Yan
Yifu Yan

Reputation: 6116

If you want to run the query as a string:

sql <- translate_sql(c("value1", "value2", "value3"))

query <- paste(
    "select *
from db.table
where column in",
    as.character(sql)
)

# or

glue::glue("select *
from db.table
where column in {as.character(sql)}")

It will be translated into

>writeLines(query)
select *
from db.table
where column in ('value1', 'value2', 'value3')

Upvotes: 0

boshek
boshek

Reputation: 4416

A handy function for this is glue_sql which works like this:

library(glue)
library(dbplyr)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
colnames(iris) <- gsub("[.]", "_", tolower(colnames(iris)))
DBI::dbWriteTable(con, "iris", iris)

values <- translate_sql(c("value1", "value2", "value3"))

glue_sql("select *
         from db.table
         where column in {values}",
         .con = con)
#> <SQL> select *
#> from db.table
#> where column in '(''value1'', ''value2'', ''value3'')'

Created on 2019-09-10 by the reprex package (v0.3.0)

Upvotes: 0

Related Questions