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?
I haven't tried this through dplyr, but the query as presented won't run in SQL Server Management Studio. Was it dplyr that wrapped your common table expression in parentheses? If so, I'd suggest not using dplyr to execute an external statement, and instead use DBI::dbGetQuery
It is indeed dplyr::tbl function that wrapped the CTE in parentheses. Your suggestion works indeed, but the data.frame is then no longer a remote tbl object but a local table (stored in memory).
Is there a way to use dbGetQuery and store the result as a remote table (i.e. not stored in local memory of my pc)?