How to work with CTE. There is some error related to anchor.

Question

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

Answer 1

String constants have datatype CHAR, not VARCHAR in SQL Server.

You need to use explicit cast:

WITH CTE (PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, DISPLAY, SORT, DEPTH)
        AS
        (
            SELECT PK_CATEGORY_ID, [DESCRIPTION], FK_CATEGORY_ID, CAST('-' AS VARCHAR(1000)) 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