SQL Server CTE hierarchy data type conversion

Question

I have created a SQL Server hierarchy which is working fine.

However I have problem in the hierarchy level data type handling which requires decimal addition.

I tried that this query below works in adding decimals and working perfectly.

SELECT
    CAST('4.1' AS DEC(3,1)) + CAST('0.1' AS DEC(3,1)) ; --correct result: 4.02

However in my CTE hierarchy query I'm getting the error as shown below. I think the error is in line:

 CAST(parent.[OWNER LEVEL] AS DEC(3,1)) + CAST(0.1 AS DEC(3,1)) AS [OWNER LEVEL],

Error:

SQL Error [240] [S0001]: Types don't match between the anchor and the recursive part in column "OWNER LEVEL" of recursive query "CTE1".

Code:

WITH CTE1 AS 
( 
    SELECT 
        CAST('4.1' AS DEC(3,1)) as [OWNER LEVEL],
        [OWNER ID], 
        [OWNER NAME],
        CAST('4.2' AS DEC(3,1)) as [OWNED LEVEL],
        [OWNED ID], 
        [OWNED NAME]
    FROM 
        OWNER_OWNED
    
    UNION ALL
    
    SELECT 
        CAST(parent.[OWNER LEVEL] AS DEC(3,1)) + CAST(0.1 AS DEC(3,1)) AS [OWNER LEVEL],
        child.[OWNER ID], 
        child.[OWNER NAME],
        CAST(parent.[OWNED LEVEL] AS DEC(3,1)) + CAST(0.1 AS DEC(3,1)) AS [OWNED LEVEL],
        child.[OWNED ID], 
        child.[OWNED NAME]
    FROM 
        OWNER_OWNED child
    INNER JOIN 
        CTE1 parent ON parent.[OWNED ID] = child.[OWNER ID] 
)
SELECT * FROM CTE1

`CAST('4.1' AS DEC(3,1))` why the quotes? – Charlieface Feb 17 '21 at 09:36 — Charlieface, Feb 17 '21 at 09:36

score 0 · Answer 1 · answered Feb 17 '21 at 08:31

Everytime you have a recursive query and a column that "absorbes" many values with anykind of operator (addition, concatenate...) you must cast the anchor value to the maximum of the datatype.

In your case, cast the [OWNER LEVEL] in the anchor query to DECIMAL(38,2).

SQL Server CTE hierarchy data type conversion

1 Answers1