How to use WITH Clause in Query Layer

Question

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.

JeffHaney · Answer

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.

JoshuaBixby · Answer

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

cjms · Answer

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