I am trying to implement a dataflow in Azure Data Factory (similar the workflow described in the answer on this question) which uses a custom SQL-query containing a WITH-statement, as a source (querying an Azure SQL DB), like:
WITH a AS (
SELECT
--magic
)
SELECT
...
FROM a
...
which leads to an "incorrect syntax" error in ADF
However specifying the subquery in the FROM statement like:
SELECT ...
FROM (
SELECT
--magic
) AS a
...
works fine, but is arguably not as readable/comprehensible (especially with multiple stacked subqueries).
Another workaround is taking the subquery from the WITH-statement, and moving it to a separate transformation-step in ADF altogether; however for lots of small subqueries this will create quite a bit of overhead/intransparency.
Which begs the question: are WITH-statements possible in ADF, and even if so, what are the exact SQL-syntax rules which ADF allows?
The only thing I can find in the Data Flow docs, is that ORDER BYs are not allowed, but no mention of other restrictions that might apply; separate questions here on Stackoverflow suggest that fore example recursive queries are also not supported.
Would be glad for some pointers, thanks!
