SQL Server Performance

CTE within a CTE

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by rklimes, Aug 14, 2007.

  1. rklimes New Member

    Is it possible to have a CTE within a CTE? or would I have to have a derived table within a CTE expression? have provided simple example.

    WITH CTE_1 as(
    WITH CTE_2 as (
    sql expression
    )
    select * from CTE_2
    join tbl_1)
    select * from CTE_1

    rklimes, Aug 14, 2007
    #2

  2. satya Moderator

    satya, Aug 15, 2007
    #3

  3. FrankKalis Moderator

    Hm...
    I don't have the time right now, but I can get it to work quickly:
    USE tempdb;
    GO
    IF OBJECT_ID('tempdb..#t') > 0
    DROP TABLE #t
    GO
    CREATE TABLE #t
    (
    id INT,
    reportToID INT NULL,
    )
    INSERT INTO #t SELECT 1, NULL
    UNION ALL SELECT 2, 1
    UNION ALL SELECT 3, 1
    UNION ALL SELECT 4, 2
    GO
    WITH MyCTE (id, reportToID, Level)
    AS
    (
    WITH MyCTE2 (id, reportToID, Level)
    AS
    (
    SELECT t1.ID, t1.reportToID, 0 AS Level
    FROM #t AS t1
    WHERE reportToID IS NULL
    UNION ALL
    SELECT t1.ID, t1.reportToID, t2.Level + 1
    FROM #t AS t1
    JOIN MyCTE2 AS t2
    ON t1.reportToID = t2.ID
    );
    SELECT *
    FROM MyCTE2
    )
    SELECT *
    FROM MyCTE
    GO
    Am I missing something?
    FrankKalis, Aug 15, 2007
    #4

  4. rklimes New Member

    When I try to run this i receive the following error. I made no changes to the code, copied it right from the post.
    Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'WITH'.
    Msg 319, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
    Msg 102, Level 15, State 1, Line 18
    Incorrect syntax near ')'.

    rklimes, Aug 15, 2007
    #5

  5. satya Moderator

    satya, Aug 15, 2007
    #6

  6. FrankKalis Moderator

    Oops, sorry. That was a freudian slip. I meant to write "can't get it to work". I do, of course, receive the same errors as you.
    FrankKalis, Aug 15, 2007
    #7

  7. Jack Vamvas Member

    You could do that one as:
    WITH CTE_2 (myCol1,myCol2)as
    (
    sql expression
    ),
    CTE_1(myCol1,myCol2) as
    (select * from CTE_2
    )
    select * from CTE_1
    join tbl_1
    Jack Vamvas, Aug 23, 2007
    #8

  8. rklimes New Member

    Thanks Jack, works great!
    rklimes, Aug 23, 2007
    #9

Share This Page