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.