Can I use multiple "with"?

SqlTsqlSql Server-2008

Sql Problem Overview


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?

Sql Solutions


Solution 1 - Sql

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.

Solution 2 - Sql

Yes - just do it this way:

WITH DependencedIncidents AS
(
  ....
),  
lalala AS
(
  ....
)

You don't need to repeat the WITH keyword

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestioncndView Question on Stackoverflow
Solution 1 - SqlTomek SzpakowiczView Answer on Stackoverflow
Solution 2 - Sqlmarc_sView Answer on Stackoverflow