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

Filed under:
|
|

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

Related posts about sql

Related posts about sql-server-2005