CTE error: "Types don't match between the anchor and the recursive part"
SqlSql ServerTsqlCommon Table-ExpressionSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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 - Sql
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 - Sql
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 - Sql
;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 - Sql
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
Solution 6 - Sql
I would recommend using nvarchar(max)
WITH CTE AS (
SELECT x,x_name FROM (VALUES (1,CAST('' AS nvarchar(MAX)))) AS test(x,x_name)
UNION ALL
SELECT x + 1 x, CONCAT(x_name,x+1) FROM CTE WHERE x < 10 )
SELECT * FROM CTE
Solution 7 - Sql
If you use CONCAT in the recursive term of a rcte, since the output type of concat is varchar(MAX), you only need to cast the column in the initial query:
WITH rcte AS (
SELECT 1 AS nr, CAST('1' AS varchar(MAX)) AS trail
UNION ALL
SELECT nr+1, CONCAT(trail, '/', nr+1)
FROM rcte
WHERE nr < 5
)
SELECT * FROM rcte;
Solution 8 - Sql
WITH rcte AS (
SELECT 1 AS nr, CAST('1' AS varchar(MAX)) AS trail
UNION ALL
SELECT nr+1, cast(CONCAT(trail, '/', nr+1) as varchar(max))
FROM rcte
WHERE nr < 5
)
SELECT * FROM rcte;
Solution 9 - Sql
;with tmp1(NewsId,DataItem ,HeaderText)
as
(
select NewsId, LEFT(HeaderText, CHARINDEX(',',HeaderText+',')-1),
STUFF(HeaderText, 1, CHARINDEX(',',HeaderText+','), '')
from Currentnews
union all
select NewsId, LEFT(HeaderText, CHARINDEX(',',HeaderText+',')-1),
STUFF(HeaderText, 1, CHARINDEX(',',HeaderText+','), '')
from tmp1
where HeaderText > ''
)
select NewsId, DataItem
from tmp1
order by NewsId