Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
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.7k 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!

30.5k questions

32.6k answers

500 comments

108k users

Browse Categories

...