CODEWITHSUNDEEP
sqlsql-servert-sql
OFRBG
OFRBG

Reputation: 1778

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

In a SQL Server stored procedure, this CTE is being run. The summary of the problem is that the query didn't have explicit casts, but even after adding them, SQL Server still throws:

Types don't match between the anchor and the recursive part in column "FName" of recursive query "ABC".

I've tried several variations of casting, such as using varchar(8000) and wrapping the CASE statement in a cast expression.

;WITH ABC (FId, FName) AS
(
    SELECT cast(1 as int), CAST('' AS varchar(max))
    UNION ALL
    SELECT
        cast(B.FId + 1 as int),
        CASE WHEN LEN(B.FName) > 0
            THEN cast((B.FName + ':' +  A.FName) as varchar(max))
            ELSE cast(A.FName as varchar(max))
        END
    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
)

Executable with context:

CREATE TABLE #T1
(
    FId     INT
,   FName   VARCHAR(max)
)

INSERT INTO #T1 VALUES
(1, '5 Oct 2020'),
(2, '12 Oct 2020'),
(3, '19 Oct 2020'),
(4, '26 Oct 2020'),
(5, '2 Nov 2020'),
(6, '9 Nov 2020'),
(7, '16 Nov 2020'),
(8, '23 Nov 2020'),
(9, '30 Nov 2020'),
(10,'7 Dec 2020'),
(11,'14 Dec 2020')

;WITH ABC (FId, FName) AS
(
    SELECT cast(1 as int), CAST('' AS varchar(max))
    UNION ALL
    SELECT
        cast(B.FId + 1 as int),
        CASE WHEN LEN(B.FName) > 0
            THEN cast((B.FName + ':' +  A.FName) as varchar(max))
            ELSE cast(A.FName as varchar(max))
        END
    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

Upvotes: 1

Views: 182

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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

Upvotes: 2

Related Questions