CTE error: "Types don't match between the anchor and the recursive part"

Question

I am executing the following statement:

;WITH cte AS (
  SELECT 
    1 as rn, 
    'name1' as nm
  UNION ALL
  SELECT 
    rn + 1,
    nm = 'name' + CAST((rn + 1) as varchar(255))
  FROM cte a WHERE rn < 10)
SELECT * 
FROM cte

...which finishes with the error...

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

Where am I making the mistake?

Solution 1

Exactly what it says:

'name1' has a different data type to 'name' + CAST((rn+1) as varchar(255))

Try this (untested)

;with cte as
(
select 1 as rn, CAST('name1' as varchar(259)) as nm
union all
select rn+1,nm = 'name' + CAST((rn+1) as varchar(255))
from cte a where rn<10)
select * from cte

Basically, you have to ensure the length matches too. For the recursive bit, you may have to use CAST('name' AS varchar(4)) if it fails again

Solution 2

You need to cast both nm fields

;with cte as
(
select  1 as rn, 
        CAST('name1' AS VARCHAR(255)) as nm
union all
select  rn+1,
        nm = CAST('name' + CAST((rn+1) as varchar(255)) AS VARCHAR(255))
from cte a where rn<10)
select * from cte

Solution 3

For me problem was in different collation.

Only this helped me:

;WITH cte AS (
  SELECT 
    1 AS rn, 
    CAST('name1' AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS nm
  UNION ALL
  SELECT 
    rn + 1,
    nm = CAST('name' + CAST((rn + 1) AS NVARCHAR(255)) AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT
  FROM cte a WHERE rn < 10)
SELECT * 
FROM cte;

Hope it can help someone else.

Solution 4

;with cte as
(
select 1 as rn, 'name' + CAST(1 as varchar(255)) as nm
union all
select rn+1,nm = 'name' + CAST((rn+1) as varchar(255))
from cte a where rn<10)
select * from cte

Solution 5

In my case, I messed up the sequence of columns in top and bottom clauses of UNION ALL. And it turned out that a varchar column appeared 'under' an int one. An easy mistake to make of you have lots of columns

View more solutions

Comments

[priyanka.sarkar]

Sir, that is what just I did. CAST(1 as varchar(255))

[priyanka.sarkar]

that is what just I did. CAST(1 as varchar(255)) . A silly mistake forgot to cast.(:

[Nathan Koop]

once again Stackoverflow has answered my question before I ask it. Thanks @priyanka & @gbn

[sheldonhull]

after fighting with sql, i ended up converting both to decimal and it fixed my problem. thanks!

[bluish]

Your answer is just like the others, you could edit one of them and delete this. Future readers like me will thank you for the clarity ;)

[Saeed Neamati]

Just in case, if you still get that error, you should also make sure that the collation of your database, and the collation of your table are the same. In other words, collations should be the same in CTE recursive queries.

[zealoushacker]

This is impossible to read without proper formatting. See How do I format my code blocks

[priyanka.sarkar]

Probably he is a newbie (: I was also at one time

[Oedhel Setren]

I've been using the exact query you provided and it still gives me the same error as question states.

[Arun Vinoth - MVP]

Instead of posting Code only answer, please explain the context how this answer will help..

[bhuneshwar singh]

This will give above output without any error. as if you will execute above query it will give you error in sql. hence I have used cast function to make it error free

[Yoosaf Abdulla]

Thanks a lot!!! this was the solution that worked for me. a very easy overlook