I'm having a problem using a CTE to generate a recursive recordset.
When the CTE is run on one database, it works fine. However, on a second database with the same exact schema (checked using RedGate SQL Compare) it generates the following error - "Types don't match between the anchor and the recursive part in column "nSort" of recursive query "t".
I've tried just about every variation of the line in question, such as...
CAST(t.nSort + '|' + c.category_name AS varchar(MAX))
t.nSort + '|' + CONVERT(varchar(MAX),c.cat
...using variations of CAST and CONVERT with different datatypes on both the anchor and recursive side of the UNION, moved the pipe around etc... but even a simple...
CAST(c.category_name AS varchar(MAX))
I'm stumped here. I can't see why it would work on one database but not the other when they have the same schema. Maybe some dodgy data is messing it up, but if that's the case, why isn't the CAST fixing it?
Thanks in advance!
WITH t AS (
SELECT 0 AS category_id,
CAST(0 AS varchar(MAX)) AS nPath,
0 AS treeLevel,
CAST('home' AS varchar(MAX)) AS nSort
CAST(c.category_id AS varchar(MAX)) + '|' + t.nPath,
t.treeLevel + 1,
t.nSort + CAST('|' + c.category_name AS varchar(MAX))
INNER JOIN category c ON (c.category_memberof = t.category_id)
ORDER BY nSort