healthcareai-r
healthcareai-r copied to clipboard
db_read does not support CTEs
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 theWITH
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 theWITH
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)
Props to @taylorlarsen for pointing me to dbSendQuery and for the starter vignette.
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?