Cte Error: "Types Don't Match Between the Anchor and the Recursive Part"

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

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

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

The info you want is all in the documentation:

When concatenating two char, varchar, binary, or varbinary expressions, the length of the resulting expression is the sum of the lengths of the two source expressions, up to 8,000 bytes.

snip ...

When comparing two expressions of the same data type but different lengths by using UNION, EXCEPT, or INTERSECT, the resulting length is the longer of the two expressions.

The precision and scale of the numeric data types besides decimal are fixed. When an arithmetic operator has two expressions of the same type, the result has the same data type with the precision and scale defined for that type.

However, a recursive CTE is not the same as a normal UNION ALL:

The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.

So in answer to your questions:

• 'Hello world!' has the data type varchar(12) by default.
• 'A' + 'B' has the data type varchar(2) because that is the sum length of the two data types being summed (the actual value is not relevant).
• n+1 is still an int
• In a recursive CTE, the data type must match exactly, so '1' is a varchar(1). If you specify varchar without a length in a CAST then you get varchar(30), so txt + ', ' + CAST(n+1 AS varchar) is varchar(33).

When you cast the anchor part to varchar(max), that automatically means the recursive part will be varchar(max) also. You don't need to cast to max, you could also cast the recursive part directly to varchar(30) for example:

WITH CTE(n, txt) AS
(
    --SELECT      1, '1'                          --This does not work.
    SELECT      1, CAST('1' AS varchar(30))         --This does work.
    --SELECT      1, CAST('1' AS varchar(1000))   --This does not work.
    UNION ALL
    SELECT      
        n+1, 
        CAST(CONCAT(txt, ', ', n+1) AS varchar(30))
    FROM        
        CTE
    WHERE       
        n < 10
)
SELECT *
FROM CTE

db<>fiddle

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

CHARINDEX() returns a BIGINT when used with a VARCHAR(MAX) rather than an INT so you need the anchor types to also be BIGINT:

;with cte as ( 
    select cast(0 as bigint) a, cast(1 as bigint) b  

Types don't match between the anchor and the recursive part after casting

With recursive CTEs the typing is very picky, especially with strings.

The reason for the differing behaviour seems to be a difference in your server's and the column's (default) collation.

Try something along this:

;WITH ABC (FId, FName) AS
(
    SELECT cast(1 as int), CAST('' AS varchar(max)) COLLATE DATABASE_DEFAULT
    UNION ALL
    SELECT
        cast(B.FId + 1 as int),
        CAST(CASE WHEN LEN(B.FName) > 0
            THEN B.FName + ':' +  A.FName
            ELSE A.FName END as varchar(max)) COLLATE DATABASE_DEFAULT --I use one cast for the whole CASE expression
    FROM (
        SELECT
            Row_Number() OVER (ORDER BY FId) AS RN,
            FName
        FROM #T1
    ) A 
    INNER JOIN ABC B ON A.RN = B.FId AND A.RN <= 5
) SELECT * FROM ABC

CTE “Types don't match between the anchor and the recursive part”

Finally I found the problem. The database was imported with compatibility level SQL Server 2012 (110).

I changed it to SQL Server 2016 (130) and now the cte works.

CTE keeps saying Type dont match between anchor and the recursive part

In a recursive CTE the types in the union all have to match exactly. So, if you have an nvarchar(max) in the top part of the union, the corresponding attribute in the bottom part also has to be nvarchar(max), not nvarchar(10). Same goes for decimal datatypes; a decimal(5, 3) doesn't match a decimal(15, 3).

This, of course, gets more complicated when types are not immediately identifiable, especially when doing concatenations and calculations. In those cases you want to wrap the concat/calculation in a convert: CONVERT(DECIMAL(15, 3), T1.DecimalAttribute/T2.DecimalAttribute) or CONVERT(NVARCHAR(MAX), T1.Text + T2.Text)

cte recursion error don't match between anchor

You have just few mistakes

WITH Series AS 
( 
  SELECT CAST(-5 AS Decimal(5, 2)) AS Number 
  UNION ALL 
  SELECT CAST((number + 0.5) AS Decimal(5, 2))    AS Number
  FROM Series s
  WHERE s.Number  <= 4.5
) 
SELECT  Number, 
      ROW_NUMBER() OVER(ORDER BY Number ASC) AS id
FROM Series 

dbfiddle demo

---

Related Topics