Menu
I am executing the following statement:
;WITH cte AS ( SELECT 1 as rn, 'name1' as nm UNION ALL SELECT rn + 1, nm = 'name' + CAST((rn + 1) as varchar(255)) FROM cte a WHERE rn < 10) SELECT * FROM cte ...which finishes with the error...
Msg 240, Level 16, State 1, Line 2 Types don't match between the anchor and the recursive part in column "nm" of recursive query "cte". Where am I making the mistake?
400
If a query defines a CTE with a particular name, the CTE takes precedence over tables, etc. A CTE can be recursive or non-recursive. A recursive CTE is a CTE that references itself. A recursive CTE can join a table to itself as many times as necessary to process hierarchical data in the table.
Recursion occurs because of the query referencing the CTE itself based on the Employee in the Managers CTE as input. The join then returns the employees who have their managers as the previous record returned by the recursive query. The recursive query is repeated until it returns an empty result set.
In general, a recursive CTE has three parts: An initial query that returns the base result set of the CTE. The initial query is called an anchor member. A recursive query that references the common table expression, therefore, it is called the recursive member.
Recursive CTE queries do have a reliance on the unique parent/child keys in order to get the best performance. If this is not possible to achieve, then a WHILE loop is potentially a much more efficient approach to handling the recursive query.
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
77
You need to cast both nm fields
;with cte as ( select 1 as rn, CAST('name1' AS VARCHAR(255)) as nm union all select rn+1, nm = CAST('name' + CAST((rn+1) as varchar(255)) AS VARCHAR(255)) from cte a where rn<10) select * from cte If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
