SQL Server – CTE – Error – Types don’t match between the anchor and the recursive part in the column "ColumnName" of recursive query "CTEName"

In the past 2-3 months, I have been working mostly working on T-SQL. Recently I was working on Common Table Expressions (CTE) and as part of that I was writing a recursive CTE and got a weird error as below:

Types don’t match between the anchor and the recursive part in the column “ColumnName” of recursive query “CTEName”

Here is what I did. Let’s work through it again with sample data and queries.

Create a table and insert some sample data.


IF OBJECT_ID('dbo.Employee') IS NOT NULL
 DROP TABLE dbo.Employee
GO

CREATE TABLE dbo.Employee (
 EmployeeId INT NOT NULL
 , EmployeeName NVARCHAR(255) NOT NULL
 , ManagerId INT NULL
 , CONSTRAINT PK_Employee_EmployeeId PRIMARY KEY CLUSTERED
 (
  EmployeeId ASC
 )
)
GO

INSERT INTO dbo.Employee (EmployeeId, EmployeeName, ManagerId)
VALUES
 (1, 'Norman Brewer', 2)
 , (2, 'Abdul Morin', 4)
 , (3, 'Denton Bauer', 4)
 , (4, 'Beau Zamora', 7)
 , (5, 'Jin Witt', 7)
 , (6, 'Charles Cabrera', 7)
 , (7, 'Jesse Kennedy', 9)
 , (8, 'Tucker Fields', 9)
 , (9, 'Randall Joseph', 10)
 , (10, 'Colton Mcfarland', NULL)
 , (11, 'Upton Livingston', 12)
 , (12, 'Chester Stone', 14)
 , (13, 'Keefe Porter', 14)
 , (14, 'Amir Norris', 17)
 , (15, 'Arden Walls', 17)
 , (16, 'Zeus Rogers', 17)
 , (17, 'Ali Adkins', 19)
 , (18, 'Harding Fulton', 19)
 , (19, 'Anthony Le', 20)
 , (20, 'Edan Mcclain', NULL)
GO

*Sample data generated using GENERATEDATA.com.

Now, here is the Recursive CTE Query that I wrote.


WITH EmployeeHierarchy (EmployeeId, EmployeeName, ManagerId, ManagerName) AS
(
      SELECT EmployeeId, EmployeeName, 0 AS ManagerId, N'' AS ManagerName
      FROM Employee
      WHERE ManagerId IS NULL -- Get the Entire Hierarchy

      UNION ALL

      SELECT Emp.EmployeeId, Emp.EmployeeName, Emp.ManagerId, EmpHier.EmployeeName AS ManagerName
      FROM
   Employee AS Emp
   INNER JOIN EmployeeHierarchy EmpHier
    ON Emp.ManagerId = EmpHier.EmployeeId
)

SELECT EmployeeId, EmployeeName, ManagerId, ManagerName
FROM EmployeeHierarchy
GO

After executing the above query, I got the following error.

CTE Data Type Mismatch Error

As we can see from the above query, since EmployeeName is of NVARCHAR data type and hence I have used an empty string of NVARCHAR type in the first query which is getting the information of the top most employee. Still it gave me this error. After playing around with the query for sometime, I found that the Recursive CTE expects the length of the columns also to be same and not just data types. I modified the above query by adding a CAST around the empty string as shown below. Which executes successfully.


WITH EmployeeHierarchy (EmployeeId, EmployeeName, ManagerId, ManagerName) AS
(
      SELECT EmployeeId, EmployeeName, 0 AS ManagerId, CAST('' AS NVARCHAR(255)) AS ManagerName
      FROM Employee
      WHERE ManagerId IS NULL -- Get the Entire Hierarchy

      UNION ALL

      SELECT Emp.EmployeeId, Emp.EmployeeName, Emp.ManagerId, EmpHier.EmployeeName AS ManagerName
      FROM
   Employee AS Emp
   INNER JOIN EmployeeHierarchy EmpHier
    ON Emp.ManagerId = EmpHier.EmployeeId
)

SELECT EmployeeId, EmployeeName, ManagerId, ManagerName
FROM EmployeeHierarchy
GO

And that’s it!

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on August 12, 2012, in Issues 'N' Resolution, SQL Server, T-SQL and tagged , , , , , , , . Bookmark the permalink. 1 Comment.

  1. good hint..

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s