Types don't match between the anchor and the recursive part in column "ColumnName" of recursive query "CTE"

Question

Trying to replicate Concatenation of multiple rows into one string-->https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#Toc205129485

here is the query:

WITH CTE (id, CodeList, Code, Length)
AS (SELECT CAST(d.id AS NVARCHAR(MAX)) AS Id,
           CAST('' AS NVARCHAR(MAX)) AS CodeList,
           CAST('' AS NVARCHAR(MAX)) AS Code,
           0 AS Length
    FROM Letters d
        INNER JOIN Cat c
            ON c.ID = Cat_Sub
    GROUP BY d.id
    UNION ALL
    SELECT CAST(dp.id AS NVARCHAR(MAX)) AS Id,
          CAST(
           CAST(CodeList AS NVARCHAR(MAX))
                + CASE
                      WHEN CAST(Length AS NVARCHAR(MAX)) = CAST('0' AS NVARCHAR(MAX)) THEN
                          CAST('' AS NVARCHAR(MAX))
                      ELSE
                          CAST(', ' AS NVARCHAR(MAX))
                  END 
                 ?????+ CAST(cp.Code AS NVARCHAR(MAX))?????? 
                  AS NVARCHAR(MAX)
                  ) AS CodeList,


           CAST(c.Code AS NVARCHAR(MAX)) AS Code,
           c.Length + 1
    FROM CTE c
        INNER JOIN Letters dp
            ON c.id= dp.id
        INNER JOIN Cat cp
            ON cp.ID = dp.id
    WHERE cp.Code > c.Code
   )
SELECT *
FROM CTE;

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

I know this is a very often asked question but I can't get from where I get my error. After testing I discovered that it comes when I add a piece of code in between question marks. It has been casted but it still gives an error

why don't You try to add this line between question marks to each of case statements? CAST('' AS NVARCHAR(MAX)) + CAST(cp.Code AS NVARCHAR(MAX)) ... ? — Whencesoever, Apr 20 '18 at 06:34
Why do you need to cast Length to NVARCHAR(MAX) to check for = 0 ? — paparazzo, Apr 20 '18 at 09:20

score 0 · Answer 1 · answered Apr 20 '18 at 12:17

Finally, I have solved the problem with another logic:

WITH Ranked ( CategoryId, rnk, ProductName )  
             AS ( SELECT CategoryId,
                         ROW_NUMBER() OVER( PARTITION BY CategoryId ORDER BY CategoryId ),
                         CAST( ProductName AS VARCHAR(8000) ) 
                    FROM Northwind..Products),
   AnchorRanked ( CategoryId, rnk, ProductName )  
             AS ( SELECT CategoryId, rnk, ProductName 
                    FROM Ranked
                   WHERE rnk = 1 ),
RecurRanked ( CategoryId, rnk, ProductName ) 
             AS ( SELECT CategoryId, rnk, ProductName 
                    FROM AnchorRanked
                   UNION ALL 
                  SELECT Ranked.CategoryId, Ranked.rnk,
                         RecurRanked.ProductName + ', ' + Ranked.ProductName
                    FROM Ranked
                   INNER JOIN RecurRanked 
                      ON Ranked.CategoryId = RecurRanked.CategoryId 
                     AND Ranked.rnk = RecurRanked.rnk + 1 )
SELECT CategoryId, MAX( ProductName ) 
      FROM RecurRanked
  GROUP BY CategoryId;

Types don't match between the anchor and the recursive part in column "ColumnName" of recursive query "CTE"

1 Answers1