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

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,

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)

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

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.