R Markdown - SQL R - Mixing Variables

[Viten]

Hello guys,

I would like to know your opinion,
I am trying to blend r and SQL. What I would like is to extract from a SQL Table (in an Oracle Server) filtering just the countries that I am interested to analyze provided by an R array.

Is this possible?
What is the best way to mix r variables with SQL queries

Here the example:

``{r}

Country_List <- c("Spain","Italy","France","Belgium")

``

``{sql, connection=con, output.var="Data"}

SELECT ID, Company,Country
FROM Data
WHERE Country IN Country_List

``

Thank you very much

[andresrcs]

Here is a nice walk around solution that uses glue::glue_sql() to prepare the array to be passed to the SQL chunk as an R variable

Using multiple R variables in SQL chunk

I had the same problem today and I think I found a solution using glue_sql() from the {glue} . To quote the glue_sql() help page: If you place a * at the end of a glue expression the values will be collapsed with commas. This is useful for the SQL IN Operator for instance. Now, this sounds useful indeed [grafik] And here is the rmarkdown code: ```{r} library(DBI) library(glue) library(RSQLite) con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE) cyl_int <- c(4L, 6L) cyl_sql <- glue_sql("{cyl_int*}") ``` ```{sql, connection=con} SELECT * FROM mtcars WHERE cyl IN (?cyl_sql) LIMIT 3 ```