Hi,
I manage to read in regular sql statements with the following code:
SQL_dwh <- readr::read_file('sql/dwh.sql')
(test1 <- dplyr::tbl(con, dplyr::sql(SQL_dwh)))
However, when the SQL code makes use of the SQL Server clause WITH, the following error is generated.
Error: nanodbc/nanodbc.cpp:1617: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ';'. [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
<SQL> 'SELECT *
FROM (;WITH
a AS
(
SELECT 1 AS ID, 'Yes' AS Reply
UNION
SELECT 2 AS ID, 'Yes' AS Reply
UNION
SELECT 3 AS ID, 'No' AS Reply
UNION
SELECT 4 AS ID, 'No' AS Reply
)
SELECT *
FROM a) "zzz14"
WHERE (0 = 1)'
Does dplyr support the WITH clause to read in data from a database?
Kind regards,