db_read does not support CTEs

[Aylr]

Background

It appears that dplyr (or an underlying library) does not support SQL CTEs. Since they are useful in creating maintainable code and are an HC standard, we should provide some alternative.

Ideas

• In db_read, parse incoming SQL for the WITH keyword and provide a nice warning with a link to a vignette that shows alternatives.
• Create a vignette that shows how to use DBI::dbSendQuery
• Maybe (I'm uncertain about this idea quality and ramifications) create an alternative function that can use DBI::dbSendQuery automatically if the WITH keyword is found. I'm imagine this could break things like the dplyr in memory...

Vignette Starter

library(healthcareai)
library(RPostgreSQL)

con_string <- build_connection_string(server = "localhost", database = "SAM")
con <- dbConnect(odbc::odbc(), .connection_string = con_string)

query <- "
WITH Step1CTE AS (
  SELECT
  a
  ,b
  ,c
  FROM SAM.Test.TestTable
)

SELECT
c
FROM Step1CTE
WHERE
(a = 10 OR b = 40)
"

cursor <- dbSendQuery(con, train_query)
df <- odbc::dbFetch(cursor)

[Aylr]

Props to @taylorlarsen for pointing me to dbSendQuery and for the starter vignette.

[michaellevy]

Thanks for finding this and writing up @Aylr. I agree this is important to at least have warnings and docs around this. @mmastand has been leading the database connections functionality. @mmastand what would you think of targeting the first two bullet points here for v2.1?