Unsupported CTE clause: WITH

[sulimanbenhalim]

First of all, I would like to express my appreciation for your work on the SQL4CDS plugin for XRMToolBox. It has been an incredibly useful tool for querying Dynamics 365 data.

I'm running into issues using Common Table Expressions (CTEs) in SQL queries due to the lack of support in SQL4CDS. Could you please consider adding support for CTEs in a future release of SQL4CDS?

[MarkMpn]

It's something I've considered, but haven't seen much demand for yet. Could you share an example of a query you're wanting to use CTEs in?

[sybaris]

Hi,

I have the same request.
And I agree that you provide a very very usefull tool with SQL4CDS.

Here the simpliest CTE :

WITH MyCTE (connectionreferenceid) AS (
    SELECT connectionreferenceid
    FROM connectionreference
)
select * from MyCTE

Result is :

Unsupported CTE clause: WITH MyCTE (connectionreferenceid) AS (
    SELECT connectionreferenceid
    FROM connectionreference
)

But the original CTE I wanted to do was :

WITH CTE (connectionreferenceid) AS (
    SELECT distinct cr.connectionreferenceid
    FROM connectionreference cr
    JOIN solutioncomponent sc1 ON sc1.objectid = cr.connectionreferenceid
    JOIN solution s1 ON s1.solutionid = sc1.solutionid
    WHERE s1.friendlyname LIKE 'MyCompany%'
)

select cte.connectionreferenceid,count( distinct s1.friendlyname)
from CTE cte
join dependency D on cte.connectionreferenceid=D.requiredcomponentobjectid
join workflow W on W.workflowid=D.dependentcomponentobjectid
JOIN solutioncomponent sc1 ON sc1.objectid = W.workflowid
JOIN solution s1 ON s1.solutionid = sc1.solutionid
where s1.friendlyname != 'Solution par défaut'  and s1.friendlyname != 'Solution active'
group by cte.connectionreferenceid

The goal of this query was to be able to find if a connectionreference is used in more than 1 solution, to check dependencies...

Thanks for advance.
Regards
Sybaris

[MarkMpn]

In this case you can rewrite the query using a subquery instead of a CTE:

select cte.connectionreferenceid,count( distinct s1.friendlyname)
from (
    SELECT distinct cr.connectionreferenceid
    FROM connectionreference cr
    JOIN solutioncomponent sc1 ON sc1.objectid = cr.connectionreferenceid
    JOIN solution s1 ON s1.solutionid = sc1.solutionid
    WHERE s1.friendlyname LIKE 'MyCompany%'
) cte
join dependency D on cte.connectionreferenceid=D.requiredcomponentobjectid
join workflow W on W.workflowid=D.dependentcomponentobjectid
JOIN solutioncomponent sc1 ON sc1.objectid = W.workflowid
JOIN solution s1 ON s1.solutionid = sc1.solutionid
where s1.friendlyname != 'Solution par défaut'  and s1.friendlyname != 'Solution active'
group by cte.connectionreferenceid

[trobblob]

This may or may not be a valid reason for developing CTE functionality:

I have a three level hierarchy of territories. Each user belonging to a territory is able to access records associated with their territory and all territories below in the hierarchy. To see which territories users have access to, I imagined that something like this would do the trick:

with cte as (
    select
    s.fullname,
    t1.name [t1],
    t2.name [t2],
    t3.name [t3]
    from territory t1
    inner join systemuser s on
    t1.territoryid = s.territoryid
    left join territory t2 on
    t1.territoryid = t2.parentterritoryid
    left join territory t3 on
    t2.territoryid = t3.parentterritoryid
)

select fullname, t1 from cte union all
select fullname, t2 from cte union all
select fullname, t3 from cte

Thanks for the work you do!

[MarkMpn]

I am still planning to add CTE support, I’m hoping to start this in the next month or so.

For a query like yours where you’ve got a fixed number of hierarchy levels you’re looking at you can rewrite this with subqueries - in fact this is how SQL Sever would execute the query by essentially copying the CTE definition into each place you’re referring to it in the main query.

Although using a CTE instead of subqueries makes this much more readable, the real power of a CTE comes when you want to query a variable number of levels in a hierarchy using recursive CTEs. I’m hoping to be able to support this over the next few updates.

[sulimanbenhalim]

Hi Mark,

I have simple example in mind where CTEs are irreplaceable is navigating hierarchical data, like location levels in Dynamics CRM.

let’s consider a Locations table with LocationID, ParentLocationID, LocationLevel, and Country.

WITH LocationHierarchy AS (
    SELECT LocationID, ParentLocationID, LocationLevel, 0 AS Depth
    FROM Locations WHERE LocationID = 'Root-Location-ID'
    UNION ALL
    SELECT l.LocationID, l.ParentLocationID, l.LocationLevel, lh.Depth + 1
    FROM Locations l INNER JOIN LocationHierarchy lh ON l.ParentLocationID = lh.LocationID
)
SELECT Depth, LocationLevel FROM LocationHierarchy ORDER BY Depth;

I think this is difficult to achieve without CTEs and would be really a game-changer for dealing with hierarchical data in Dynamics.

what do you think?

[MarkMpn]

Absolutely, this is exactly why it's needed. I've made a start, it won't be in the next update but hopefully another month or so.