How to use WITH Clause in Query Layer

cjms

I have query that uses common tables via a WITH clause. It works fine in SSMS, but doesn't seem to run in the Add Query Layer tool. Simple example below:

WITH my_cte AS (
    SELECT *
    FROM my_table
)
SELECT *
FROM my_cte

 Invalid SQL syntax[[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WITH'.]

Is there some sort of restriction or syntax difference for CTE tables in ArcGIS Pro?

This query is being run against a separate SQL Server 2022 database via an SDE connection, not an Enterprise Geodatabase.

DavidSolari

Not sure why that keyword specifically is causing issues but you can rewrite that as:

SELECT a.*
FROM (SELECT * FROM my_table) a