|
|
Error Message
Server: Msg 252, Level 16, State 1, Line 1
Recursive common table expression '<CTE Name>' does not
contain a top-level UNION ALL operator.
Causes
A common table expression (CTE), introduced in SQL Server 2005, can be thought of as a temporary result set that is defined with 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 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>
From this general syntax of a recursive common table expression, this error message can be encountered in 2 different ways. The first method is if the anchor member of the recursive CTE is missing and just the recursive member is present. The second method of generating this error is if UNION is used instead of UNION ALL. Both UNION and UNION ALL operators combine the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The difference between UNION and UNION ALL is that UNION ALL incorporates all rows into the results, which includes duplicates while UNION removes duplicate rows.
To illustrate on how this error message may be encountered, the following table structure is used:
CREATE TABLE [dbo].[Folder] (
[FolderID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[FolderName] VARCHAR(100) NOT NULL,
[ParentFolderID] INT NULL REFERENCES [dbo].[Folder]( [FolderID] )
)
INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Inetpub' )
INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Program Files' )
INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Windows' )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'wwwroot', 1 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'Microsoft Office', 2 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'Microsoft SQL Server', 2 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'assembly', 3 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'system32', 3 )
The following recursive common-table expression (CTE) tries to return all folders defined in the [dbo].[Folder] table together with the name of the parent folder (if it exists) as well as the full path of the current folder. The definition of the following recursive common-table expression will generate this error as the anchor member is missing and just the recursive member is present:
WITH FullFolder ( [FolderID], [FolderName], [ParentFolderName], [FullFolderPath] ) AS (
SELECT A.[FolderID], A.[FolderName], ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName],
ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName] + '\' + A.[FolderName]
FROM [dbo].[Folder] A INNER JOIN FullFolder B
ON A.[ParentFolderID] = B.[FolderID]
)
SELECT * FROM FullFolder
ORDER BY ParentFolderName, FullFolderPath
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'FullFolder' does not contain a top-level UNION ALL operator.
Another way of generating this error as mentioned earlier is with the use of the UNION operator instead of the UNION ALL operator, as can be seen from the definition of the following recursive CTE:
WITH FullFolder ( [FolderID], [FolderName], [ParentFolderName], [FullFolderPath] ) AS (
SELECT [FolderID], [FolderName], CAST('' AS VARCHAR(MAX)) AS [ParentFolderName],
CAST('\' + [FolderName] AS VARCHAR(MAX))
FROM [dbo].[Folder]
WHERE [ParentFolderID] IS NULL
UNION
SELECT A.[FolderID], A.[FolderName], ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName],
ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName] + '\' + A.[FolderName]
FROM [dbo].[Folder] A INNER JOIN FullFolder B
ON A.[ParentFolderID] = B.[FolderID]
)
SELECT * FROM FullFolder
ORDER BY ParentFolderName, FullFolderPath
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'FullFolder' does not contain a top-level UNION ALL operator.
Solution / Work Around:
As the message suggests, to avoid this error from happening, make sure that the UNION ALL operator is used instead of the UNION when defining a recursive common-table expression (CTE). Also, make sure that the anchor member of the recursive CTE is included together with the recursive member.
Here’s an updated version of the recursive CTE defined earlier that returns all folders defined in the [dbo].[Folder] table together with the name of the parent folder (if it exists) as well as the full path of the current folder which uses the UNION ALL operator and which includes the anchor member:
WITH FullFolder ( [FolderID], [FolderName], [ParentFolderName], [FullFolderPath] ) AS (
SELECT [FolderID], [FolderName], CAST('' AS VARCHAR(MAX)) AS [ParentFolderName],
CAST('\' + [FolderName] AS VARCHAR(MAX))
FROM [dbo].[Folder]
WHERE [ParentFolderID] IS NULL
UNION ALL
SELECT A.[FolderID], A.[FolderName], ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName],
ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName] + '\' + A.[FolderName]
FROM [dbo].[Folder] A INNER JOIN FullFolder B
ON A.[ParentFolderID] = B.[FolderID]
)
SELECT * FROM FullFolder
ORDER BY ParentFolderName, FullFolderPath
The result of this recursive CTE is as follows:
FolderID FolderName ParentFolderName FullFolderPath
---------- ----------------------- ----------------- ---------------
1 Inetpub \Inetpub
2 Program Files \Program Files
3 Windows \Windows
4 wwwroot \Inetpub \Inetpub\wwwroot
5 Microsoft Office \Program Files \Program Files\Microsoft Office
6 Microsoft SQL Server \Program Files \Program Files\Microsoft SQL Server
7 assembly \Windows \Windows\assembly
8 system32 \Windows \Windows\system32
|