SQL subquery with CTE

Question

I have a hierarchical query with Common Table Expressions:

WITH Revision(REV, MonitoringGroupId, BusinessLineId, REVTYPE, REVEND) AS
(
    SELECT REV, MonitoringGroupId, BusinessLineId, REVTYPE, REVEND
    FROM MonitoringGroupBusinessLine_AUD
    WHERE REV = 1045
    UNION ALL
    SELECT rev.REV, rev.MonitoringGroupId, rev.BusinessLineId, rev.REVTYPE, rev.REVEND
    FROM MonitoringGroupBusinessLine_AUD rev
    INNER JOIN Revision ON rev.REVEND = Revision.REV
)
SELECT 
    DISTINCT
    MonitoringGroupId, 
    BusinessLineId, 
    IIF(REVTYPE = 2, 'REMOVE', 'ADD') as Operation
FROM Revision

And I want to use it in as a sub-query, something like that:

SELECT audit.MonitoringGroupId, audit.BusinessLineId, audit.Operation
FROM
(
    WITH Revision(REV, MonitoringGroupId, BusinessLineId, REVTYPE, REVEND) AS
    (
        SELECT REV, MonitoringGroupId, BusinessLineId, REVTYPE, REVEND
        FROM MonitoringGroupBusinessLine_AUD
        WHERE REV = 1045
        UNION ALL
        SELECT rev.REV, rev.MonitoringGroupId, rev.BusinessLineId, rev.REVTYPE, rev.REVEND
        FROM MonitoringGroupBusinessLine_AUD rev
        INNER JOIN Revision ON rev.REVEND = Revision.REV
    )
    SELECT 
        DISTINCT
        MonitoringGroupId, 
        BusinessLineId, 
        IIF(REVTYPE = 2, 'REMOVE', 'ADD') as Operation
    FROM Revision
) audit

I have an error:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

I tried to add ; before WITH, but it still does not work. How to use my hierarchical query as a subquery ?

Well, as you have learned, SQL Server doesn't support that syntax. — Gordon Linoff, Aug 28 '18 at 17:07
Why do you want to do that? You can already query the CTE as you would a table, what does using it as a subquery accomplish? — Error_2646, Aug 28 '18 at 17:10

score 1 · Accepted Answer · answered Aug 28 '18 at 17:12

You make the subquery in the output.

;WITH Revision(REV, MonitoringGroupId, BusinessLineId, REVTYPE, REVEND) AS
(
    SELECT REV, MonitoringGroupId, BusinessLineId, REVTYPE, REVEND
    FROM MonitoringGroupBusinessLine_AUD
    WHERE REV = 1045
    UNION ALL
    SELECT rev.REV, rev.MonitoringGroupId, rev.BusinessLineId, rev.REVTYPE, rev.REVEND
    FROM MonitoringGroupBusinessLine_AUD rev
    INNER JOIN Revision ON rev.REVEND = Revision.REV
)
SELECT audit.MonitoringGroupId, audit.BusinessLineId, audit.Operation
FROM
(
    SELECT 
        DISTINCT
        MonitoringGroupId, 
        BusinessLineId, 
        IIF(REVTYPE = 2, 'REMOVE', 'ADD') as Operation
    FROM Revision
) audit

SQL subquery with CTE

1 Answers1