Wednesday, April 6, 2011

Name of CTE and referenced table should be different

Just got the below example from the public forum.

CREATE TABLE Batch (Batch CHAR(1),Status INT)

INSERT INTO Batch VALUES ('A',1)
INSERT INTO Batch VALUES ('B',2)
INSERT INTO Batch VALUES ('C',3)

WITH Batch AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Batch, Status) AS RowNum
FROM Batch
)
DELETE FROM Batch
WHERE RowNum=1

As you can see , running the query SQL Server throws the error.
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'Batch' does not contain a top-level UNION ALL operator.

SQL Server "thinks" that the CTE referenced to itself but there is no UNION ALL clause.In the blow example CTE named EmpCTE referenced within to itself to join with Employees table.

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(

-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = 7
UNION ALL

-- Recursive Member (RM)
SELECT E.empid, E.empname, E.mgrid, M.lvl+1
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

Finally,in order to resolve the problem you need that CTE and user table have different names. Something like that

WITH Batch_cte AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY Batch, Status) AS RowNum
FROM Batch
)
DELETE FROM Batch_cte
WHERE RowNum=1

No comments: