Can I concat with a r variale within a SQL chunk in an Rmd file?

[dougfir]

Example Rmd file:

---
title: "Untitled"
output: html_document
---

\```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
\```

## R

\```{r cars}
library(tidyverse)
library(dbplyr)
library(DBI)
library(glue)
iris_db <- tbl_memdb(iris)
example_con <- src_memdb()$con
var1 <- "setosa"
\```

## SQL Chunks With Variable

### works fine, returns a new df some_df
\```{sql connection=example_con, include=FALSE, output.var="some_df"}
select 
  `Sepal.Width`,
  *
from iris
where Species = ?var1
\```

### does not run
\```{sql connection=example_con, include=FALSE, output.var="some_other_df"}
select 
  `Sepal.Width` as concat("Sepal.Width", "-", ?var1),
  --`Sepal.Width` as paste0("Sepal.Width", "-", ?var1), -- tried this too
  *
from iris
where Species = ?var1
\```

Desired outcome is to return a new df, 'some_other_df' with field:
Sepal.Width-setosa where 'setosa' has been appended to a alias from an r variable. Is this possible?

[jlacko]

Having a formula in place of an alias (as opposed to an actual calculation) is tricky.
Have you considered building the query string in a first step, either by pasting or glueing, and then and only then passing it to a database?

[dougfir]

@jlacko Doing this in an r script as opposed to a Rmd chunk I did use glue (With some help from this board) but was just curious if there was a simple way within the context of a SQL chunk. Do you know if it's possible to embed glue code within a sql chunk? Backup option is your suggestion of building up the string in r as a variable first before passing as the alias.

[jlacko]

If I were in your place I would consider something along these lines:

---
title: "Untitled"
output: html_document
---

# R

\```{r iris}
library(tidyverse)
library(dbplyr)
library(DBI)
library(glue)
iris_db <- tbl_memdb(iris)
example_con <- src_memdb()$con
parameter <- "asdf"

sql_string <- glue("select count(1) as {parameter} from iris ") # or what not...
\```

## SQL Chunk from `code` block

\```{sql connection=example_con, code = sql_string}

\```

With the sql_string variable glued together as necessary.

This seems to work; I am not assigning the result of the sql query to an object, just printing it out.

I would be more comfortable handling the query as a piece of string & calling it afterwards - it certainly makes the process easier to debug, if nothing else.

[dougfir]

Thanks for the suggestion. I'm going to leave this open, hoping to be able to stay inside a sql chunk rather than a string but thanks for the suggestion!

[jlacko]

Sure thing; your code = your rules

But out of curiosity: what is the benefit of going the way of a complicated chunk with glued aliases, as opposed to plain old DBI::getQuery(), when you are storing the results in an output variable anyways?
I understand the benefit of sql output to a markdown table, but I don't get this.

[dougfir]

Hi @jlacko, a few reasons. I like being able to seamlessly move from r to sql in the same doc - the sql chunk includes sql syntax highlighting. The query is likely to require updating as the project evolves and editing sql rather than a string is convenient.

[jlacko]

Oki, I get that. Syntax highlighting alone is a good enough reason. Thank you for indulging my curiosity!