How to work with CTE. There is some error related to anchor.
- by Shantanu Gupta
I am creating a hierarchy representaion of a column. But an error occurs
Details are
Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor
and the recursive part in column
"DISPLAY" of recursive query "CTE".
I know there is some typecasting error. But I dont know how to remove error. Please just dont only sort out my error. I need explanation why this error is coming. When this error occurs.
I am trying to sort table on the basis of sort col that i m introducing.
I want to add '-' at every level and want to sort accordingly.
Please help
WITH CTE (PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, DISPLAY, SORT, DEPTH)
AS
(
SELECT PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, '-' AS DISPLAY, '--' AS SORT, 0 AS DEPTH
FROM dbo.L_CATEGORY_TYPE WHERE FK_CATEGORY_ID IS NULL
UNION ALL
SELECT T.PK_CATEGORY_ID, T.[DESCRIPTION], T.FK_CATEGORY_ID, CAST(DISPLAY+T.[DESCRIPTION] AS VARCHAR(1000)), '--' AS SORT, C.DEPTH +1
FROM dbo.L_CATEGORY_TYPE T JOIN CTE C ON C.PK_CATEGORY_ID = T.FK_CATEGORY_ID
--SELECT T.PK_CATEGORY_ID, C.SORT+T.[DESCRIPTION], T.FK_CATEGORY_ID
--, CAST('--' + C.SORT AS VARCHAR(1000)) AS SORT, CAST(DEPTH +1 AS INT) AS DEPTH
--FROM dbo.L_CATEGORY_TYPE T JOIN CTE C ON C.FK_CATEGORY_ID = T.PK_CATEGORY_ID
)
SELECT PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, DISPLAY, SORT, DEPTH
FROM CTE
ORDER BY SORT
