CODEWITHSUNDEEP
sqlsql-server
Mario
Mario

Reputation: 14780

Types don't match between the anchor and the recursive part in column

I have this query in SQL Server 2012 which works, but I have changed the order (Id, Slug, CategoryName) to (Id, CategoryName, Slug) in order to match the returned data and column names and now I get this error:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "CategoryName" of recursive query "categoryPath"

This is the query:

WITH categoryPath(Id, CategoryName, Slug)
AS
(
    SELECT 
        Id, CategoryName, Slug 
    FROM Categories
    WHERE ParentCategoryId IS NULL

    UNION ALL

    SELECT 
        Categories.Id,
        CAST(categoryPath.CategoryName + ' / ' + categories.CategoryName AS NVARCHAR(150)),
        categories.Slug
    FROM Categories
    JOIN categoryPath ON Categories.ParentCategoryId = categoryPath.Id
)
SELECT * 
FROM [ItemCategories]
JOIN categoryPath ON [ItemCategories].CategoryId = categoryPath.Id 
WHERE [ItemCategories].StockId = '5'

Upvotes: 1

Views: 2192

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

When I get this error, I just cast galore and it soon works:

WITH categoryPath(Id, CategoryName, Slug) AS (
      SELECT Id, cast(CategoryName as nvarchar(150)), cast(Slug as nvarchar(150))
      FROM Categories
      WHERE ParentCategoryId IS NULL
      UNION ALL
      SELECT Categories.Id,
             CAST(categoryPath.CategoryName + ' / ' + categories.CategoryName AS NVARCHAR(150)),
             CAST(categories.Slug as nvarchar(150))
      FROM Categories JOIN
           categoryPath
           ON Categories.ParentCategoryId=categoryPath.Id
     )
. . .

Upvotes: 3

Related Questions