|
|
Error Message
Server: Msg 460, Level 16, State 1, Line 1
DISTINCT operator is not allowed in the recursive part
of a recursive common table expression '<Common Table
Expression 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 SELECT DISTINCT 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].[Files] (
[FileID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[FileName] VARCHAR(100),
[FolderName] VARCHAR(100),
[ParentFolderName] VARCHAR(100)
)
GO
INSERT INTO [dbo].[Files] ( [FileName], [FolderName], [ParentFolderName] )
VALUES ('readme.txt', '', '')
INSERT INTO [dbo].[Files] ( [FileName], [FolderName], [ParentFolderName] )
VALUES ('default.html', '', '')
INSERT INTO [dbo].[Files] ( [FileName], [FolderName], [ParentFolderName] )
VALUES ('error.log', 'users', '')
INSERT INTO [dbo].[Files] ( [FileName], [FolderName], [ParentFolderName] )
VALUES ('login.ini', 'users', '')
INSERT INTO [dbo].[Files] ( [FileName], [FolderName], [ParentFolderName] )
VALUES ('system.ini', 'administrator', 'users')
INSERT INTO [dbo].[Files] ( [FileName], [FolderName], [ParentFolderName] )
VALUES ('login.txt', 'docs', 'administrator')
The following recursive common-table expression (CTE) tries to return all folders defined in the [dbo].[Files] table together with parent folder name (if it exists) and the level of the folder within the folder hierarchy.
WITH FolderHierarchy AS (
SELECT DISTINCT CAST('\' AS VARCHAR(MAX)) AS [FolderName],
CAST([ParentFolderName] AS VARCHAR(MAX)) AS [ShortFolderName],
1 AS [FolderLevel]
FROM [dbo].[Files]
WHERE [ParentFolderName] = '' AND [FolderName] = ''
UNION ALL
SELECT DISTINCT CAST(B.[FolderName]+ A.[FolderName] + '\' AS VARCHAR(MAX)),
CAST(A.[FolderName] AS VARCHAR(MAX)) AS [ShortFolderName],
B.[FolderLevel] + 1
FROM [dbo].[Files] A INNER JOIN FolderHierarchy B
ON A.[ParentFolderName] = B.[ShortFolderName] AND
A.[FolderName] != ''
)
SELECT * FROM FolderHierarchy
ORDER BY [FolderName]
The following result set is what is expected from the recursive common-table expression (CTE):
FullFolderName ShortFolderName FolderLevel
--------------------------- ---------------- -----------
\ 1
\users\ users 2
\users\administrator\ administrator 3
\users\administrator\docs\ docs 4
But since the recursive CTE used the DISTINCT clause in the recursive part of the recursive common table expression, the following error message is generated:
Msg 460, Level 16, State 1, Line 1
DISTINCT operator is not allowed in the recursive part of a recursive common
table expression 'FolderHierarchy'.
Solution / Work Around:
Removing the DISTINCT clause from the recursive part of the recursive common table expression will avoid the error message but duplicates will be included in the output:
-- Removing DISTINCT -- Different Result -- Includes Duplicates
WITH FolderHierarchy AS (
SELECT DISTINCT CAST('\' AS VARCHAR(MAX)) AS [FolderName],
CAST([ParentFolderName] AS VARCHAR(MAX)) AS [ShortFolderName],
1 AS [FolderLevel]
FROM [dbo].[Files]
WHERE [ParentFolderName] = '' AND [FolderName] = ''
UNION ALL
SELECT CAST(B.[FolderName]+ A.[FolderName] + '\' AS VARCHAR(MAX)),
CAST(A.[FolderName] AS VARCHAR(MAX)) AS [ShortFolderName],
B.[FolderLevel] + 1
FROM [dbo].[Files] A INNER JOIN FolderHierarchy B
ON A.[ParentFolderName] = B.[ShortFolderName] AND
A.[FolderName] != ''
)
SELECT * FROM FolderHierarchy
ORDER BY [FolderName]
The output of this recursive common table expression is as follows:
FullFolderName ShortFolderName FolderLevel
--------------------------- ---------------- -----------
\ 1
\users\ users 2
\users\ users 2
\users\administrator\ administrator 3
\users\administrator\ administrator 3
\users\administrator\docs\ docs 4
\users\administrator\docs\ docs 4
One possible way to work around this error is to hide the DISTINCT clause within a sub-query, as shown in the following recursive CTE:
-- Using DISTINCT in a sub-query
WITH FolderHierarchy AS (
SELECT DISTINCT CAST([FolderName] AS VARCHAR(MAX)) AS [FolderName], 1 AS [FolderLevel]
FROM [dbo].[Files]
WHERE [ParentFolderName] = '' AND [FolderName] = ''
UNION ALL
SELECT CAST(B.[FolderName] + '\' + A.[FolderName] AS VARCHAR(MAX)), B.[FolderLevel] + 1
FROM (SELECT DISTINCT [FolderName], [ParentFolderName] FROM [dbo].[Files]) A
INNER JOIN FolderHierarchy B
ON A.[ParentFolderName] = B.[FolderName] AND
A.[FolderName] != ''
)
SELECT * FROM FolderHierarchy
ORDER BY [FolderName]
GO
Unfortunately, the same error message will be generated:
Msg 460, Level 16, State 1, Line 1
DISTINCT operator is not allowed in the recursive part of a recursive common
table expression 'FolderHierarchy'.
There are four (4) possible ways of overcoming this error message and still generate the desired output. The first option, which is the simplest one, is to move the DISTINCT clause outside the recursive CTE and place it in the external SELECT statement that calls the CTE, as can be seen in the following statement:
-- Option #1 : DISTINCT in External SELECT Statement
WITH FolderHierarchy AS (
SELECT DISTINCT CAST('\' AS VARCHAR(MAX)) AS [FolderName],
CAST([ParentFolderName] AS VARCHAR(MAX)) AS [ShortFolderName],
1 AS [FolderLevel]
FROM [dbo].[Files]
WHERE [ParentFolderName] = '' AND [FolderName] = ''
UNION ALL
SELECT CAST(B.[FolderName]+ A.[FolderName] + '\' AS VARCHAR(MAX)),
CAST(A.[FolderName] AS VARCHAR(MAX)) AS [ShortFolderName],
B.[FolderLevel] + 1
FROM [dbo].[Files] A INNER JOIN FolderHierarchy B
ON A.[ParentFolderName] = B.[ShortFolderName] AND
A.[FolderName] != ''
)
SELECT DISTINCT * FROM FolderHierarchy
ORDER BY [FolderName]
The second option is to create a view that will return the distinct folder names and parent folder names from the [dbo].[Files] table. Then this view is used in the recursive part of the recursive common table expression:
-- Option #2 : Create a View
CREATE VIEW [dbo].[Folders] AS
SELECT DISTINCT [FolderName], [ParentFolderName]
FROM [dbo].[Files]
GO
WITH FolderHierarchy AS (
SELECT DISTINCT CAST('\' AS VARCHAR(MAX)) AS [FullFolderName],
CAST([FolderName] AS VARCHAR(MAX)) AS [ShortFolderName],
1 AS [FolderLevel]
FROM [dbo].[Files]
WHERE [ParentFolderName] = '' AND [FolderName] = ''
UNION ALL
SELECT CAST(B.[FullFolderName] + A.[FolderName] + '\' AS VARCHAR(MAX)),
CAST(A.[FolderName] AS VARCHAR(MAX)), B.[FolderLevel] + 1
FROM [dbo].[Folders] A INNER JOIN FolderHierarchy B
ON A.[ParentFolderName] = B.[ShortFolderName] AND
A.[FolderName] != ''
)
SELECT * FROM FolderHierarchy
ORDER BY [FullFolderName]
GO
The third option is to create a table-valued function that will return the unique folder names under a given parent folder. Then use this table-valued function in the recursive part of the recursive CTE, as shown in the following script:
-- Option #3 : Create a Table-Valued Function
CREATE FUNCTION [dbo].[ufn_GetFolders] ( @ParentFolderName VARCHAR(MAX) )
RETURNS TABLE
AS RETURN (SELECT DISTINCT [FolderName]
FROM [dbo].[Files]
WHERE [ParentFolderName] = @ParentFolderName AND
[FolderName] != '')
GO
WITH FolderHierarchy AS (
SELECT DISTINCT CAST('\' AS VARCHAR(MAX)) AS [FullFolderName],
CAST([FolderName] AS VARCHAR(MAX)) AS [ShortFolderName],
1 AS [FolderLevel]
FROM [dbo].[Files]
WHERE [ParentFolderName] = '' AND [FolderName] = ''
UNION ALL
SELECT CAST(A.[FullFolderName] + B.[FolderName] + '\' AS VARCHAR(MAX)),
CAST(B.[FolderName] AS VARCHAR(MAX)), A.[FolderLevel] + 1
FROM FolderHierarchy A CROSS APPLY [dbo].[ufn_GetFolders] ( A.[ShortFolderName] ) B
)
SELECT * FROM FolderHierarchy
ORDER BY [FullFolderName]
GO
The fourth option is to use multiple common table expressions. The first CTE will simply return the unique folder names and parent folder names. The second CTE will then use this non-recursive CTE in its recursive part, as can be seen in the following script:
-- Option #4 : Use Multiple CTE's
WITH UniqueFolders AS (
SELECT DISTINCT [FolderName], [ParentFolderName]
FROM [dbo].[Files]
),
FolderHierarchy AS (
SELECT DISTINCT CAST('\' AS VARCHAR(MAX)) AS [FullFolderName],
CAST([FolderName] AS VARCHAR(MAX)) AS [ShortFolderName],
1 AS [FolderLevel]
FROM [dbo].[Files]
WHERE [ParentFolderName] = '' AND [FolderName] = ''
UNION ALL
SELECT CAST(B.[FullFolderName] + A.[FolderName] + '\' AS VARCHAR(MAX)),
CAST(A.[FolderName] AS VARCHAR(MAX)) AS [ShortFolderName],
B.[FolderLevel] + 1
FROM UniqueFolders A INNER JOIN FolderHierarchy B
ON A.[ParentFolderName] = B.[ShortFolderName] AND
A.[FolderName] != ''
)
SELECT [FullFolderName], [FolderLevel]
FROM FolderHierarchy
ORDER BY [FullFolderName]
GO
|