I tried to get the first row for a WHERE statement in a recursive table expression. Sadly i'm getting this error:

The TOP or OFFSET operator is not allowed in the recursive part of a recursive common table expression 'cteTree'.

Here's my SQL Query:

WITH cteTree AS( 
    SELECT 
        cct.CategoryID AS bla, 
        ct.CategoryID,ct.ParentCategoryID, 
        ct.Name, 
        ct.Published, 
        CASE 
            WHEN EXISTS(SELECT ProductID FROM Product WHERE ProductID = (SELECT TOP(1) ProductID FROM ProductCategory WHERE ProductCategory.CategoryID = ct.CategoryID) AND Product.Published = 1) THEN 1 
            ELSE 0 
        END AS ProductExists, 
        1 As Cycle
            FROM Category AS ct 
            LEFT JOIN Category AS cct ON cct.ParentCategoryID = ct.CategoryID 
            WHERE cct.CategoryID IS NULL 
    UNION ALL 
    SELECT 
        cct.CategoryID AS bla, 
        ct.CategoryID,
        ct.ParentCategoryID, 
        ct.Name, 
        ct.Published, 
        CASE 
            WHEN cct.ProductExists = 1 THEN 1 
            WHEN EXISTS(SELECT ProductID FROM Product WHERE ProductID = (SELECT TOP(1) ProductID FROM ProductCategory WHERE ProductCategory.CategoryID = ct.CategoryID) AND Product.Published = 1) THEN 1 
            ELSE 0 
        END AS ProductExists, 
        Cycle   1
            FROM Category AS ct 
            JOIN cteTree AS cct ON ct.CategoryID = cct.ParentCategoryID 
)
SELECT * FROM cteTree

The problem is in the second Case statement under UNION ALL.

SELECT ProductID FROM Product WHERE ProductID = (SELECT TOP(1) ProductID FROM ProductCategory WHERE ProductCategory.CategoryID = ct.CategoryID) AND Product.Published = 1

Does someone know if there's another expression for selecting the first row in a recursive Table expression that works?

CodePudding user response：

There are some tricks to use TOP (1) in a CTE, such as using ROW_NUMBER instead, or putting it into a TVF. But in your case you can just use a normal join:

You should also use NOT EXISTS instead of the LEFT JOIN IS NULL construct.

WITH cteTree AS( 
    SELECT 
        cct.CategoryID AS bla, 
        ct.CategoryID,ct.ParentCategoryID, 
        ct.Name, 
        ct.Published, 
        CASE 
            WHEN EXISTS (SELECT 1
                FROM Product p
                JOIN ProductCategory pc ON pc.CategoryID = ct.CategoryID
                WHERE p.Published = 1
            ) THEN 1 
            ELSE 0 
        END AS ProductExists, 
        1 As Cycle
    FROM Category AS ct 
    WHERE NOT EXISTS (SELECT 1
        FROM Category AS cct
        WHERE cct.ParentCategoryID = ct.CategoryID 
    )
    UNION ALL 
    SELECT 
        cct.CategoryID AS bla, 
        ct.CategoryID,
        ct.ParentCategoryID, 
        ct.Name, 
        ct.Published, 
        CASE 
            WHEN cct.ProductExists = 1 THEN 1 
            WHEN EXISTS (SELECT 1
                FROM Product p
                JOIN ProductCategory pc ON pc.CategoryID = ct.CategoryID
                WHERE p.Published = 1
            ) THEN 1 
            ELSE 0 
        END AS ProductExists, 
        Cycle   1
    FROM Category AS ct 
    JOIN cteTree AS cct ON ct.CategoryID = cct.ParentCategoryID 
)
SELECT *
FROM cteTree