Can I Use Multiple "With"?
Just for Example: with Dependencedincidents as ( Select Inc. [Rectime], Inc. [Sql] as [Str] from ( Select A. [Rectime] as [Rectime], X. [Sql] as [Sql] from...
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?
2 Answers
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
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
