Menu
To have multiple WITH clauses, you do not need to specify WITH multiple times. Rather, after the first WITH clause is completed, add a comma, then you can specify the next clause by starting with <query_name> followed by AS. There is no comma between the final WITH clause and the main SQL query.
You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition.
The WITH clause in SQL was introduced in standard SQL to simplify complex long queries, especially those with JOINs and subqueries. Often interchangeably called CTE or subquery refactoring, a WITH clause defines a temporary data set whose output is available to be referenced in subsequent queries.
The WITH clause allows you, as part of your select statement, to assign a name to a subquery and utilise its results by referencing that name. It is, on first glance, quite jarring. Because the subquery factoring clause brutally transforms the look of a query, making it no longer start with the SELECT keyword.
Try:
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
)
And yes, you can reference common table expression inside common table expression definition. Even recursively. Which leads to some very neat tricks.
Yes - just do it this way:
WITH DependencedIncidents AS
(
....
),
lalala AS
(
....
)
You don't need to repeat the WITH keyword
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With