Sep 18, 2013

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


If we will execute following CTE sql query in sql server:

;WITH cteSquare
AS(
     SELECT 1 AS Num, 1.0 AS [Square] --Anchor
     UNION ALL
     SELECT Num + 1 , (Num + 1) * (Num + 1) -- recursive part
     FROM cteSquare WHERE [Square] < 144
)SELECT * FROM cteSquare

We will get error nessage :

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

Cause: It is necessary that data types of each select list of anchor of CTE (Common table expression) must be exactly same as recursive part. In above example data type of Square in anchor is numeric while data type of Square in recursive part is integer.

Solution:

Use same data type of Square in both anchor and recursive part. For example:

;WITH cteSquare
AS(
     SELECT 1 AS Num, 1 AS [Square] --Anchor
     UNION ALL
     SELECT Num + 1 , (Num + 1) * (Num + 1) -- recursive part
     FROM cteSquare WHERE [Square] < 120
)SELECT * FROM cteSquare

Output:

Num
Square
1
1
2
4
3
9
4
16
5
25
6
36
7
49
8
64
9
81
10
100
11
121

No comments:

Post a Comment