Puis-je utiliser plusieurs "avec"?

Question

juste pour l'exemple

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
)

ne fonctionne pas. "Erreur de proximité Avec".

Je tiens également à utiliser d'abord avec l'intérieur de la seconde. Est-elle réelle ou j'ai besoin d'utiliser des tables temporaires ?

Answer 1

Essayez:

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
)

Et oui, vous pouvez référencer expression de table commune à l'intérieur de communes de la table de définition de l'expression. Même de manière récursive. Ce qui mène à des trucs.

Answer 2

Oui - il suffit de le faire de cette façon:

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

Vous n'avez pas besoin de répéter l' WITH mot-clé