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

sql

data-types

casting

null-handling

byNikita Barsukov·Aug 10, 2024

To fix the "Types don't match" error in a CTE, make sure the data types of every column in the anchor and recursive sections match. Use CAST or CONVERT to get an exact data type match:

WITH RecursiveCTE AS (
    SELECT CAST(AnchorColumn AS INT) AS ColumnAlias -- Don't cast int into oblivion!
    FROM InitialTable
    WHERE ConditionAnchor

    UNION ALL

    SELECT CAST(RecursiveColumn AS INT) -- Cast united!
    FROM RecursiveTable
    JOIN RecursiveCTE ON JoinCondition
    WHERE ConditionRecursive
)
SELECT * FROM RecursiveCTE;

Replace INT with your desired data type, ensuring ColumnAlias is consistent in both parts.

Verifying data types

Don't blindly CAST or CONVERT. First, know the data type landscape with sp_help:

EXEC sp_help 'InitialTable'; -- True Identity!
EXEC sp_help 'RecursiveTable'; -- Alter Ego!

Check the output and CAST accordingly ensuring accurate representation.

Importance of explicit conversions

Untangle SQL Server’s auto conversions by understanding data type precedence. Don’t depend on auto conversions in a CTE, or face the wrath of errors! Get the upper hand by having explicit CAST or CONVERT.

Stringing along

Struggling with strings types? Remember to CAST large enough:

SELECT CAST('your-name' AS VARCHAR(100)) -- Using the stretchable VARCHAR!

Avoid the dreaded sequel of data truncation by avoiding casting too short.

Dealing with strings, NULLs and collation

String Concatenation

Concatenating values? Ensure numbers pretend to be strings with CAST:

SELECT CAST(Column1 AS VARCHAR) + Column2 -- 1+"2" = "12", Hey, it's relational, not mathematical!

Handling NULLs

NULLs are like ghosts, no physical presence but can spook the SQL logic. Arm yourself with COALESCE to handle NULL values:

SELECT COALESCE(CAST(Column1 AS VARCHAR), 'Default') -- Ghostbusters to the rescue!

Collating effectively

For collation disparities, use COLLATE DATABASE_DEFAULT:

SELECT Column1 COLLATE DATABASE_DEFAULT -- We all speak the same (collate) language here!

Navigating recursion

Incremental values

Mechanically increment values in the recursive part, ensuring data types can carry the load:

SELECT CAST(RecursiveColumn + 1 AS INT) -- Here comes the increment train, toot toot!

Modifying data

Need to tweak data? Make sure the recycled values align with the data types:

UPDATE RecursiveTable
SET ModifiedColumn = CAST(NewValue AS DECIMAL(10, 2)) -- Decimals to the decimals, or they go haywire!

Manipulate data within CTE to maintain type uniformity.

explain-codes / Sql / Cte error: "Types don't match between the anchor and the recursive part"

Linked

Unioning two tables with different number of columns



How to select multiple rows filled with constants?



Optimal way to concatenate/aggregate strings



Sql Server FOR EACH Loop



What is the most efficient/elegant way to parse a flat table into a tree?



Using an Alias column in the where clause in PostgreSQL



How to use multiple WITH statements in one PostgreSQL query?



Verifying data types Importance of explicit conversions Stringing along Dealing with strings, NULLs and collation Navigating recursion