Hi,
The T-SQL (a Common Table Expression) here mentioned runs well in SSMS.
WITH
a AS
(
SELECT 'a' AS genus, 1 AS taxa
UNION ALL
SELECT 'b' AS genus, 1 AS taxa
UNION ALL
SELECT 'c' AS genus, 2 AS taxa
UNION ALL
SELECT 'd' AS genus, 1 AS taxa
UNION ALL
SELECT 'e' AS genus, 2 AS taxa
UNION ALL
SELECT 'f' AS genus, 3 AS taxa
),
b AS
(
SELECT 'a' AS genus, 1 AS code
UNION ALL
SELECT 'a' AS genus, 2 AS code
UNION ALL
SELECT 'b' AS genus, 1 AS code
UNION ALL
SELECT 'b' AS genus, 2 AS code
UNION ALL
SELECT 'c' AS genus, 3 AS code
),
c AS
(
SELECT a.genus, taxa, code
FROM a
LEFT OUTER JOIN b ON a.genus = b.genus
)
SELECT genus, taxa, code
FROM c
When connected to an SQLIte dbb I manage to read in this CTE as a remote tibble with the following code.
sql2 <- readr::read_file("sql/WITH2.sql")
(test1 <- dplyr::tbl(con, dplyr::sql(sql2)))
However, when trying the same when connected to SQL Server dbb, the code gives an error because the tbl function then wraps the WITH statement in a subquery, which is doesn't when trying the same on an SQLite dbb. How can I prevent tbl wrapping the CTE in a subquery?
The following error makes clear what tbl is doing when connected to SQL Server.
Error: nanodbc/nanodbc.cpp:1617: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'WITH'. [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. [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]Incorrect syntax near ')'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
<SQL> 'SELECT *
FROM (WITH
a AS
(
SELECT 'a' AS genus, 1 AS taxa
UNION ALL
SELECT 'b' AS genus, 1 AS taxa
UNION ALL
SELECT 'c' AS genus, 2 AS taxa
UNION ALL
SELECT 'd' AS genus, 1 AS taxa
UNION ALL
SELECT 'e' AS genus, 2 AS taxa
UNION ALL
Grateful for any help!