0

I'm new to SQL and am trying to query a view (dbo.) made from a D365 table. My goal is to do a recursive joins on 4 different views but to start with I tried something basic and received the following error: The query references an object that is not supported in distributed processing mode. Below is my code with column/datasource names changed for privacy. ANY THOUGHTS ARE APPRECIATED!!

    WITH F AS
  (SELECT ColumnA,
          ColumnB 1 AS lvl
   FROM dbo.datasource
   WHERE ColumnB IS NULL
   UNION ALL SELECT FL.ColumnA,
                    FL.ColumnB,
                    lvl + 1 AS lvl
   FROM F
   INNER JOIN dbo.datasource FL ON F.ColumnA = FL.ColumnB)
SELECT *
FROM F
Improve this question
0

2 Answers 2

Reset to default
4

My thought is that Fabric is a laughing stock of a product, and I'm sorry that you're stuck having to use it.

In Microsoft Fabric, Fabric Data Warehouse and the SQL analytics endpoint both support standard, sequential, and nested CTEs, but not recursive CTEs.

From: Nested Common Table Expression (CTE) in Fabric data warehousing

Improve this answer
1

You haven't given any details about what product you are using that returns this error.

I was able to reproduce this error with Synapse SQL Serverless and trying to access something that would require distributed processing.

The mention of "not supported in distributed processing mode" did imply that it might work in other modes though.

I did find recursive CTEs worked fine against local temp tables. If the underlying data you are trying to query is small you might be able to do something like the below. (The wrapping in EXEC is because a direct insert also throws the "not supported in distributed processing mode" error)

CREATE TABLE #T(...);

INSERT #T 
EXEC(N'
SELECT ...
FROM ...
')

/*Then use #T in a recursive CTE*/
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.