GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression ‘%.*ls’.

Error Message:
Msg 467, Level 16, State 1, Line 2
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression ‘%.*ls’.

Severity level:
16.

Description:
This error message appears when you try to use a GROUP BY or HAVING clause or a subquery in the recursive part of a common table expression.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Error of the Severity level 16 are generated by the user and can be fixed by the SQL Server user. The statement can not be run this way. You can not use a GROUP BY or HAVING clause or a subquery in the recursive part of a common table expression.

Versions:
This error message was introduced with SQL Server 2005.

Example(s):
IF OBJECT_ID(‘dbo.t’) IS NOT NULL
    DROP TABLE dbo.t
GO

CREATE TABLE dbo.t
(
    id INT,
    reportToID INT NULL,
    salary int
);
GO

INSERT INTO dbo.t
SELECT 1, NULL, 1000 UNION ALL
SELECT 2, 1, 100 UNION ALL
SELECT 3, 1, 100 UNION ALL
SELECT 4, 2, 100;
GO

WITH MyCTE (id, salary, reportToID, Level)
AS
(
SELECT
    t1.ID, t1.reportToID, t1.salary, 0 AS Level
FROM
    dbo.t AS t1
WHERE
    reportToID IS NULL
UNION ALL
SELECT
    t1.ID, SUM(t1.salary), MIN(t1.reportToID), Level
FROM
    dbo.t AS t1
    JOIN
    MyCTE AS t2 ON t1.reportToID = t2.ID
GROUP BY
    t1.ID, Level
)

SELECT
    *
FROM
    MyCTE;
GO

Remarks:
In the above example we try to use a GROUP BY clause in the recursive part of a common table expression. This raises the error.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |