Can you create nested WITH clauses for Common Table Expressions

Question

WITH y AS       WITH x AS           SELECT   FROM MyTable           SELECT   FROM x   SELECT   FROM y   Does something like this work  I tried it earlier but I couldn t get it to work

User · Answer

You can do the following  which is referred to as a recursive query   WITH y AS     SELECT x  y  z   FROM MyTable   WHERE  base condition     UNION ALL    SELECT x  y  z   FROM MyTable M   INNER JOIN y ON M  some other condition    y  some other condition    SELECT   FROM y   You may not need this functionality   I ve done the following just to organize my queries better   WITH y  AS     SELECT      FROM MyTable   WHERE  base condition     x AS     SELECT      FROM y   WHERE  something else    SELECT    FROM x

User · Answer

With does not work embedded  but it does work consecutive   WITH A AS         B AS        SELECT   FROM A UNION ALL SELECT   FROM B   EDIT Fixed the syntax     Also  have a look at the following example  SQLFiddle DEMO

User · Answer

Nested  With  is not supported  but you can always use the second With as a subquery  for example   WITH A AS                   --WITH B AS   SELECT COUNT 1  AS  CT FROM C   SELECT CASE  CT WHEN 1 THEN 1 ELSE 0 END FROM B --doesn t work                 SELECT CASE WHEN count   1 THEN 1 ELSE 0 END AS CT FROM  SELECT COUNT 1  AS count FROM dual                  union all                 select 100 AS CT from dual                            select CT FROM A

User · Answer

we can create nested cte please see the below cte in example   with cte data as    Select   from  HumanResources   Department    cte data1 as   Select   from  HumanResources   Department     select   from cte data cte data1

User · Answer

These answers are pretty good  but as far as getting the items to order properly  you d be better off looking at this article http   dataeducation com dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge  Here s an example of his query   WITH paths AS        SELECT          EmployeeID           CONVERT VARCHAR 900   CONCAT      EmployeeID        AS FullPath      FROM EmployeeHierarchyWide      WHERE ManagerID IS NULL      UNION ALL      SELECT          ehw EmployeeID           CONVERT VARCHAR 900   CONCAT p FullPath  ehw EmployeeID        AS FullPath      FROM paths AS p          JOIN EmployeeHierarchyWide AS ehw ON ehw ManagerID   p EmployeeID     SELECT   FROM paths order by FullPath

User · Answer

I was trying to measure the time between events with the exception of what one entry that has multiple processes between the start and end  I needed this in the context of other single line processes   I used a select with an inner join as my select statement within the Nth cte  The second cte I needed to extract the start date on X and end date on Y and used 1 as an id value to left join to put them on a single line    Works for me  hope this helps    cte extract as        select ps Process as ProcessEvent           ps ProcessStartDate            ps ProcessEndDate          -- select strt       from dbo tbl some table ps      inner join  select max ProcessStatusId  ProcessStatusId                      from dbo tbl some table                  where Process    some extract tbl                   and convert varchar 10   ProcessStartDate  112   lt   29991231                    strt on strt ProcessStatusId   ps ProcessStatusID     cte rls as        select  Sample  as ProcessEvent        x ProcessStartDate  y ProcessEndDate  from       select 1 as Id  ps Process as ProcessEvent           ps ProcessStartDate            ps ProcessEndDate         -- select strt       from dbo tbl some table ps      inner join  select max ProcessStatusId  ProcessStatusId                      from dbo tbl some table                  where Process    XX Prcss                   and convert varchar 10   ProcessStartDate  112   lt   29991231                    strt on strt ProcessStatusId   ps ProcessStatusID       x     left join           select 1 as Id  ps Process as ProcessEvent               ps ProcessStartDate                ps ProcessEndDate             -- select strt           from dbo tbl some table ps          inner join  select max ProcessStatusId  ProcessStatusId                     from dbo tbl some table                      where Process    YY Prcss Cmpltd                       and convert varchar 10   ProcessEndDate  112   lt   29991231                        enddt on enddt ProcessStatusId   ps ProcessStatusID               y on y Id   x Id            other ctes

User · Answer

While not strictly nested  you can use common table expressions to reuse previous queries in subsequent ones    To do this  the form of the statement you are looking for would be  WITH x AS        SELECT   FROM MyTable     y AS        SELECT   FROM x   SELECT   FROM y

[sql] Can you create nested WITH clauses for Common Table Expressions?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to nested

Examples related to common-table-expression