topic Re: How to use WITH Clause in Query Layer in ArcGIS Pro Questions

How to use WITH Clause in Query Layer

cjms — Wed, 29 Oct 2025 21:41:05 GMT

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.

Re: How to use WITH Clause in Query Layer

DavidSolari — Thu, 30 Oct 2025 16:12:44 GMT

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

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

Re: How to use WITH Clause in Query Layer

cjms — Thu, 30 Oct 2025 22:30:04 GMT

I'm aware subqueries work. I was just hoping not to have to refactor; I think the CTE looks more readable for longer queries.

Re: How to use WITH Clause in Query Layer

JoshuaBixby — Fri, 31 Oct 2025 14:53:17 GMT

I agree about CTE. I suggest you submit an ArcGIS Pro Idea to support using CTE with SQL Server query layers.

Re: How to use WITH Clause in Query Layer

JeffHaney — Thu, 04 Dec 2025 20:56:36 GMT

I have found table-valued functions to be the best way to sneak non vanilla SQL into Pro. Lets me use CTEs and things like ROW_NUMBER to create unique ids.