5
```
{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
library(tidyverse)
library(odbc)
library(DBI)
library(dbplyr)
```

```{sql, connection=con, output.var="df"}
SELECT DB_Fruit.Pear, Store.Name, Cal.Year, Sales.Qty FROM DB_Fruit
```
#> Error: unexpected symbol in "SELECT DB_Fruit.Pear"

I'm attempting to run SQL code in an R Markdown chunk as shown above. I'm getting the "unexpected symbol" error shown above. My best guess is that I need to escape the underscore with something such as \_ or \\_ but neither of those makes my error go away.

If I instead query using DBI (shown below) I do not get any errors:

df <- dbGetQuery(con,'
  SELECT DB_Fruit.Pear, Store.Name, Cal.Year, Sales.Qty 
  FROM DB_Fruit
')

Maybe the dbGetQuery function is able to interpret things such as underscores _ correctly whereas the regular R Markdown parser can't? Or maybe there's blank spaces that have been copy/pasted as some weird unicode characters that again dbGetQuery function is able to interpret whereas the regular R Markdown parser can't?

What's the likely culprit and what do I do about it?

Display name
  • 4,153
  • 5
  • 27
  • 75

2 Answers2

2

Your chunk header probably should be

{SQL, connection=con, output.var="df"}

instead of

{r SQL, connection=con, output.var="df"}

rd_nielsen
  • 2,407
  • 2
  • 11
  • 18
  • sorry but marked solved too soon, corrected the chunk header to `{sql, connection=con, output.var="df"}` and noticed immediately that the syntax highlighting turned back on, indicating this is part #1 to the solution. But alas even with this correction I still get the same error. Anything else I need to do? – Display name Jan 27 '20 at 17:48
  • 1
    Your SQL appears to refer to tables named (or aliased as) "Store", "Cal", and "Sales", but those don't appear in the FROM clause. So perhaps your SQL needs to be corrected. – rd_nielsen Jan 27 '20 at 17:59
1

You have to use "Chunk Output Inline" in the Rmarkdown document

---
editor_options: 
  chunk_output_type: inline
---
Ian.T
  • 1,016
  • 1
  • 9
  • 19
  • I am having the same problem as the OP, and I just added this code to the rmarkdown document, and it works. I don't know WHY (yet), but it it worked. – ScottyJ Dec 03 '22 at 02:34
  • Eight months later, I made the same mistake and found my comment above . For those that are interested, it seems that if the code is not run "inline", then it gets sent to the R interpreter rather than the correct context. [See this discussion with `bash` here](https://github.com/ZimmermanLab/gao-thesis/issues/1) – ScottyJ Jul 19 '23 at 22:14