How to work with CTE. There is some error related to anchor.
Posted
by Shantanu Gupta
on Stack Overflow
See other posts from Stack Overflow
or by Shantanu Gupta
Published on 2010-05-05T10:49:24Z
Indexed on
2010/05/05
10:58 UTC
Read the original article
Hit count: 304
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
© Stack Overflow or respective owner