|
|
Error Message
Server: Msg 467, Level 16, State 1, Line 1
GROUP BY, HAVING, or aggregate functions are not allowed in the
recursive part of a recursive common table expression
'<CTE Name>'.
Causes
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement.
|
A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. A CTE that references itself is referred to as a recursive common table expression.
The recursive CTE structure must contain at least one anchor member and one recursive member. The general syntax of a recursive common table expression is as follows:
WITH <CTE Name> [ ( [, ...n] ) ]
AS (
<CTE Query Definition – Anchor Member>
UNION ALL
<CTE Query Definition – Recursive Member>
)
SELECT * FROM <CTE Name>
The following items are not allowed in the common table definition query definition of a recursive member:
- SELECT DISTINCT
- GROUP BY
- HAVING
- Scalar Aggregation
- TOP
- LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
- Subqueries
- A hint applied to a recursive reference to a CTE inside a CTE_query_definition
For this particular error message, as mentioned above, using a GROUP BY or HAVING in the common table definition query definition of a recursive member will generate this error. To illustrate, the following table structure will be used with the given sample data:
CREATE TABLE [dbo].[Folders] (
[FolderID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[FolderName] VARCHAR(100),
[ParentFolderID] INT
)
GO
ALTER TABLE [dbo].[Folders]
ADD CONSTRAINT [FK_Folders_Folders] FOREIGN KEY ( [ParentFolderID] )
REFERENCES [dbo].[Folders] ( [FolderID] )
GO
CREATE TABLE [dbo].[Files] (
[FileID] INT NOT NULL PRIMARY KEY IDENTITY (1, 1),
[FileName] VARCHAR(100),
[FolderID] INT
)
GO
ALTER TABLE [dbo].[Files]
ADD CONSTRAINT [FK_Files_Folders] FOREIGN KEY ( [FolderID] )
REFERENCES [dbo].[Folders] ( [FolderID] )
GO
INSERT INTO [dbo].[Folders] ( [FolderName], [ParentFolderID] )
VALUES ( 'C:', NULL )
INSERT INTO [dbo].[Folders] ( [FolderName], [ParentFolderID] )
VALUES ( 'Program Files', 1 )
INSERT INTO [dbo].[Folders] ( [FolderName], [ParentFolderID] )
VALUES ( 'Windows', 1 )
INSERT INTO [dbo].[Folders] ( [FolderName], [ParentFolderID] )
VALUES ( 'Microsoft SQL Server', 2 )
INSERT INTO [dbo].[Folders] ( [FolderName], [ParentFolderID] )
VALUES ( 'system32', 3 )
GO
INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'install.ini', 1 )
INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'web.config', 2 )
INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'notepad.exe', 3 )
INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'sql.config', 4 )
INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'error.log', 4 )
INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'readme.txt', 5 )
INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'setup.ini', 5 )
INSERT INTO [dbo].[Files] ( [FileName], [FolderID] )
VALUES ( 'java.exe', 5 )
The following recursive common-table expression (CTE) lists all the folders displaying the full path of each folder:
WITH [FolderHierarchy] AS (
SELECT CAST([FolderName] AS VARCHAR(MAX)) AS [FolderName], [FolderID]
FROM [dbo].[Folders]
WHERE [ParentFolderID] IS NULL
UNION ALL
SELECT CAST(B.[FolderName] + '\' + A.[FolderName] AS VARCHAR(MAX)) AS [FolderName],
A.[FolderID]
FROM [dbo].[Folders] A INNER JOIN [FolderHierarchy] B
ON A.[ParentFolderID] = B.[FolderID]
)
SELECT *
FROM [FolderHierarchy]
ORDER BY [FolderName]
GO
The output of this common-table expression is as follows using the sample data shown earlier:
FolderName FolderID
-------------------------------------- -----
C: 1
C:\Program Files 2
C:\Program Files\Microsoft SQL Server 4
C:\Windows 3
C:\Windows\system32 5
The following recursive common-table expression (CTE) tries to return all folders defined in the [dbo].[Folders] table together with the number of files on each folder.
WITH [FolderHierarchy] AS (
SELECT CAST(A.[FolderName] AS VARCHAR(MAX)) AS [FolderName], A.[FolderID],
COUNT(B.[FileName]) AS [FileCount]
FROM [dbo].[Folders] A INNER JOIN [dbo].[Files] B
ON A.[FolderID] = B.[FolderID] AND
A.[ParentFolderID] IS NULL
GROUP BY CAST(A.[FolderName] AS VARCHAR(MAX)), A.[FolderID]
UNION ALL
SELECT CAST(B.[FolderName] + '\' + A.[FolderName] AS VARCHAR(MAX)) AS [FolderName],
A.[FolderID], COUNT(C.[FileName]) AS [FileCount]
FROM [dbo].[Folders] A INNER JOIN [FolderHierarchy] B
ON A.[ParentFolderID] = B.[FolderID]
INNER JOIN [dbo].[Files] C
ON A.[FolderID] = C.[FolderID]
GROUP BY CAST(B.[FolderName] + '\' + A.[FolderName] AS VARCHAR(MAX)), A.[FolderID]
)
SELECT *
FROM [FolderHierarchy]
ORDER BY [FolderName]
GO
The expected output of the CTE is as follows:
FolderName FileCount
-------------------------------------- -----------
C: 1
C:\Program Files 1
C:\Program Files\Microsoft SQL Server 2
C:\Windows 1
C:\Windows\system32 3
But since the recursive CTE uses the GROUP BY clause in the recursive part of the recursive common table expression, the following error message is raised:
Msg 467, Level 16, State 1, Line 1
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common
table expression 'FolderHierarchy'.
Solution / Work Around:
There are a couple of ways of working around this error message and still be able to accomplish the result needed from a recursive CTE. The first option is with the use of a function that will accept a folder ID and return the number of files within that folder. Here’s how the user-defined function will look like:
CREATE FUNCTION [dbo].[ufn_GetFileCount] ( @FolderID INT )
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(*) FROM [dbo].[Files]
WHERE [FolderID] = @FolderID)
END
GO
And here’s how the recursive CTE will look like using the user-defined function:
WITH [FolderHierarchy] AS (
SELECT CAST(A.[FolderName] AS VARCHAR(MAX)) AS [FolderName], A.[FolderID],
[dbo].[ufn_GetFileCount] ( A.[FolderID] ) AS [FileCount]
FROM [dbo].[Folders] A INNER JOIN [dbo].[Files] B
ON A.[FolderID] = B.[FolderID] AND
A.[ParentFolderID] IS NULL
UNION ALL
SELECT CAST(B.[FolderName] + '\' + A.[FolderName] AS VARCHAR(MAX)) AS [FolderName],
A.[FolderID], [dbo].[ufn_GetFileCount] ( A.[FolderID] ) AS [FileCount]
FROM [dbo].[Folders] A INNER JOIN [FolderHierarchy] B
ON A.[ParentFolderID] = B.[FolderID]
)
SELECT [FolderName], [FileCount]
FROM [FolderHierarchy]
ORDER BY [FolderName]
GO
Here’s the output of this recursive CTE, which is the desired output:
FolderName FileCount
-------------------------------------- -----------
C: 1
C:\Program Files 1
C:\Program Files\Microsoft SQL Server 2
C:\Windows 1
C:\Windows\system32 3
The second option in working around this error message is with the use of a view. The view will return each folder together with the number of files within each folder. Here’s the definition of the view:
CREATE VIEW [dbo].[FolderFileCount]
AS
SELECT A.[FolderID], A.[FolderName], COUNT(B.[FileName]) AS [FileCount]
FROM [dbo].[Folders] A LEFT OUTER JOIN [dbo].[Files] B
ON A.[FolderID] = B.[FolderID]
GROUP BY A.[FolderID], A.[FolderName]
GO
Here’s an updated version of the recursive CTE which uses the newly defined view:
WITH [FolderHierarchy] AS (
SELECT CAST(A.[FolderName] AS VARCHAR(MAX)) AS [FolderName], A.[FolderID],
B.[FileCount]
FROM [dbo].[Folders] A INNER JOIN [dbo].[FolderFileCount] B
ON A.[FolderID] = B.[FolderID] AND
A.[ParentFolderID] IS NULL
UNION ALL
SELECT CAST(B.[FolderName] + '\' + A.[FolderName] AS VARCHAR(MAX)) AS [FolderName],
A.[FolderID], C.[FileCount]
FROM [dbo].[Folders] A INNER JOIN [FolderHierarchy] B
ON A.[ParentFolderID] = B.[FolderID]
INNER JOIN [dbo].[FolderFileCount] C
ON A.[FolderID] = C.[FolderID]
)
SELECT [FolderName], [FileCount]
FROM [FolderHierarchy]
ORDER BY [FolderName]
GO
|