Can I use multiple “with”?

Question

Just for example

With DependencedIncidents AS

(

    SELECT INC.[RecTime],INC.[SQL] AS [str] FROM

    (

        SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A 

        CROSS JOIN [Incident] AS X

            WHERE

                patindex('%' + A.[Col] + '%', X.[SQL]) > 0

    ) AS INC

)

With lalala AS

(

    SELECT INC.[RecTime],INC.[SQL] AS [str] FROM

    (

        SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A 

        CROSS JOIN [Incident] AS X

            WHERE

                patindex('%' + A.[Col] + '%', X.[SQL]) > 0

    ) AS INC

)

doesn't work. "Error near With".

Also, I want to use first with inside second with. Is it real or I need to use temp tables?

Answer 1

Use this code:

With DependencedIncidents AS

(

    SELECT INC.[RecTime],INC.[SQL] AS [str] FROM

    (

        SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A 

        CROSS JOIN [Incident] AS X

            WHERE

                patindex('%' + A.[Col] + '%', X.[SQL]) > 0

    ) AS INC

),

lalala AS

(

    SELECT INC.[RecTime],INC.[SQL] AS [str] FROM

    (

        SELECT A.[RecTime] As [RecTime],X.[SQL] As [SQL] FROM [EventView] AS A 

        CROSS JOIN [Incident] AS X

            WHERE

                patindex('%' + A.[Col] + '%', X.[SQL]) > 0

    ) AS INC

)

Note: You can refer to the common table expression inside the common table expression definition.