SQL SERVER WITH clause causing problems when using dplyr:: sql to return a remote tbl object

[Bertusian]

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,

[nutterb]

@Bertusian

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)

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

DBI::dbGetQuery(con, SQL_dwh)

[Bertusian]

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)?

Thanx for any suggestions.