none
using a cte in 2 queries in same script RRS feed

  • Question

  • Hi we run 2017 std. I have 4 cte's followed by a number of queries in the same script as shown in the code block. 

    The 1st query uses all 4 cte's.  the 2nd query just uses the 1st cte and the rest of the queries use no cte's. 

    I can see by the red squiggly lines this isn't going to work because the 1st cte is referenced twice in 2 different queries. 

    Is there some way perhaps with semicolons to allow this approach without having to code cte 1 twice and without having to insert cte 1 into a temp table?  I tried putting a semicolon after the first query but still saw squiggly red lines.

    ;with 
    cte1 as (select...),
    cte2 as (select...),
    cte3 as (select...),
    cte4 as (select...)
    
    select ...
    into #stage
    from ...
    left join cte2 
    on...
    left join cte3
    on...
    left join cte4
    on...
    join cte1
    on ....
    
    select ...
    into #stage2
    from #stage
    ...
    join cte1
    on ...
    
    FROM THIS POINT FORWARD ALL SORTS OF separate T-SQL queries
    
    
    
    

    Thursday, December 12, 2019 8:08 PM

Answers

  • You are out of luck. You need to repeat the cte1 code for your second query.
    • Marked as answer by db042190 Friday, December 13, 2019 1:19 PM
    Thursday, December 12, 2019 8:13 PM
    Moderator
  • You are out of luck. You need to repeat the cte1 code for your second query.

    Or materialise the result in a temp table or a table variable.

    I have actually have a wishlist item for this. Votes are welcome!
    https://feedback.azure.com/forums/908035-sql-server/suggestions/32891617-module-level-table-expressions


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Friday, December 13, 2019 1:19 PM
    Thursday, December 12, 2019 10:39 PM
  • Hi db042190,

    Please check following script .

    ;with 
    cte1 as (select...),
    cte2 as (select...),
    cte3 as (select...),
    cte4 as (select...)
    
    select ...
    into #stage
    from ...
    left join cte2 
    on...
    left join cte3
    on...
    left join cte4
    on...
    join cte1
    on ....
    
    
    ;with 
    cte1 as (select...)
    select ...
    into #stage2
    from #stage
    ...
    join cte1
    on ...

    Best Regards,

    Rachel 



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by db042190 Friday, December 13, 2019 1:19 PM
    Friday, December 13, 2019 6:04 AM
  • Maybe you can define a View which is similar to cte1?

    • Marked as answer by db042190 Friday, December 13, 2019 1:20 PM
    Friday, December 13, 2019 10:18 AM

All replies

  • You are out of luck. You need to repeat the cte1 code for your second query.
    • Marked as answer by db042190 Friday, December 13, 2019 1:19 PM
    Thursday, December 12, 2019 8:13 PM
    Moderator
  • You are out of luck. You need to repeat the cte1 code for your second query.

    Or materialise the result in a temp table or a table variable.

    I have actually have a wishlist item for this. Votes are welcome!
    https://feedback.azure.com/forums/908035-sql-server/suggestions/32891617-module-level-table-expressions


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Friday, December 13, 2019 1:19 PM
    Thursday, December 12, 2019 10:39 PM
  • Hi db042190,

    Please check following script .

    ;with 
    cte1 as (select...),
    cte2 as (select...),
    cte3 as (select...),
    cte4 as (select...)
    
    select ...
    into #stage
    from ...
    left join cte2 
    on...
    left join cte3
    on...
    left join cte4
    on...
    join cte1
    on ....
    
    
    ;with 
    cte1 as (select...)
    select ...
    into #stage2
    from #stage
    ...
    join cte1
    on ...

    Best Regards,

    Rachel 



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by db042190 Friday, December 13, 2019 1:19 PM
    Friday, December 13, 2019 6:04 AM
  • Maybe you can define a View which is similar to cte1?

    • Marked as answer by db042190 Friday, December 13, 2019 1:20 PM
    Friday, December 13, 2019 10:18 AM
  • hi erland i was surprised to see only 4 votes. somewhere else at that link i saw 50 votes mentioned. either way it seems like there should be more votes.
    Friday, December 13, 2019 1:21 PM
  • hi erland i was surprised to see only 4 votes. somewhere else at that link i saw 50 votes mentioned. either way it seems like there should be more votes.
    The 50 votes is from the older Connect site from which all active suggestions and bug reports were transferred a a few years back.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 13, 2019 9:28 PM