0 votes
1 view
in SQL by (20.3k points)

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?

1 Answer

0 votes
by (40.4k points)

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. 

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...