¿Puedo utilizar varios "con"?

Sólo por ejemplo:

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
)

...no funciona. "Error cerca de Con".

Además, quiero usar primero con el segundo interior con. ¿Es real o necesito usar tablas temporales?

Mar 21 '11 16:03 cnd

Intentar:

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
)

Y sí, puede hacer referencia a una expresión de tabla común dentro de la definición de expresión de tabla común. Incluso de forma recursiva. Lo que lleva a algunos trucos muy interesantes .

Mar 21 '2011 09:03 Tomek Szpakowicz

Sí, hazlo de esta manera:

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

No es necesario repetir la WITHpalabra clave.

Mar 21 '2011 09:03 marc_s

Puedes resolver este problema usando la tabla temporal,

WITH DependencedIncidents AS
(
  ....
)

select * INTO #TempTable from DependencedIncidents 
  
with lalala AS
(
  ....
)

select * from lalala

También puedes escribir el segundo con después del con. Puedes escribir un segundo "con" usando ","".

Después de terminarlos todos, debe ejecutar "con" último valor de retorno "select * from lalala".

Feb 16 '2023 11:02 Onur Dikmen