none
Combining 2 CTEs into One Query RRS feed

  • Question

  • I am having trouble trying to combine these two CTEs into One query. Can anyone help lead me in the right direction? I tried using comma after the first CTE and removing the 'WITH" in the second CTE, but get an error "Recursive member of a common table expression 'ANS_CTE' has multiple recursive references."

    WITH ANS_CTE AS 
        (
            SELECT
                *,
                ROW_NUMBER() OVER (PARTITION BY ANS_IndividualID ORDER BY ANS_SubmitDate) AS rownum
            FROM 
                [dbo].[vCDCP_rptANSImprove]
        UNION ALL
        SELECT DISTINCT 
            A1.ANS_IndividualID,
            A1.ANS_SubmitDate,
            A1.ANS_ArrSum,
            A2.ANS_DepSum,
            CASE 
               WHEN 
    A1.ANS_ArrSum > A2.ANS_DepSum and
    not exists(select 1 from ANS_CTE A3 where A1.ANS_IndividualID = A3.ANS_IndividualID and A1.ANS_SubmitDate < A3.ANS_SubmitDate) THEN 1 ELSE 0 
            END AS flag
        FROM
            ANS_CTE AS A1
        INNER JOIN
            ANS_CTE AS A2 ON A1.ANS_IndividualID = A2.ANS_IndividualID 
                                AND A1.rownum = A2.rownum + 1
    	),
    	ATQInfant_CTE AS 
        (
            SELECT
                *,
                ROW_NUMBER() OVER (PARTITION BY ATQInfant_IndividualID ORDER BY ATQInfant_Date) AS rownum
            FROM 
                [dbo].[vCDCP_rptInfantImprove]
        )
        SELECT DISTINCT 
            A1.ATQInfant_IndividualID,
            A1.ATQInfant_Date,
            A1.InfantSum,
            A2.InfantSum,
            CASE 
               WHEN 
    A1.InfantSum > A2.InfantSum and
    not exists(select 1 from ATQInfant_CTE A3 where A1.ATQInfant_IndividualID = A3.ATQInfant_IndividualID and A1.ATQInfant_Date < A3.ATQInfant_Date) THEN 1 ELSE 0 
            END AS flag
        FROM
            ATQInfant_CTE AS A1
        INNER JOIN
            ATQInfant_CTE AS A2 ON A1.ATQInfant_IndividualID = A2.ATQInfant_IndividualID AND A1.rownum = A2.rownum + 1




    Monday, January 13, 2020 4:08 PM

All replies

  • I see  you use a self JOIN between ATQInfant_CTE query, so you ask how to JOIN ANS_CTE? Or you have trouble to build query within ANS_CTE? Can you eplaoin the logic ? Do you try to build a recursive  query ?

    CREATE TABLE Employees
    (
      empid   int         NOT NULL,
      mgrid   int         NULL,
      empname varchar(25) NOT NULL,
      salary  money       NOT NULL,
      CONSTRAINT PK_Employees PRIMARY KEY(empid),
      CONSTRAINT FK_Employees_mgrid_empid
        FOREIGN KEY(mgrid)
        REFERENCES Employees(empid)
    )
    CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
    SET NOCOUNT ON
    INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
    INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
    INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
    INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00) 
    INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
    INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
    INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)
    INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)
    INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)
    INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)
    INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
    INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
    INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
    INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

    -----------------------------
    Returning an employee (for example, Robert whose empid=7) 
    and his/her subordinates in all levels. 
    The following CTE provides a solution to this request:

    WITH EmpCTE(empid, empname, mgrid, lvl)
    AS


      -- Anchor Member (AM)
      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = 7
      UNION ALL
      
      -- Recursive Member (RM)
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees AS E
        JOIN EmpCTE AS M
          ON E.mgrid = M.empid
    )
    SELECT * FROM EmpCTE


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 14, 2020 5:17 AM
    Moderator
  • Hello friend,

    Like Uri said, you did not use ANS_CTE in the last query, but your error is related to this CTE.

    About the error , you can refer to following articles:

    https://www.itprotoday.com/development-techniques-and-management/ctes-multiple-recursive-members
    http://www.sql-server-helper.com/error-messages/msg-253.aspx

    Best regards,

    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Tuesday, January 14, 2020 9:39 AM
  • The bellow query may help you

     DECLARE @t1 TABLE (itmID int, itmIDComp int);  
    INSERT @t1 VALUES (1,10), (2,10);   
      
    DECLARE @t2 TABLE (itmID int, itmIDComp int);   
    INSERT @t2 VALUES (3,10), (4,10);   
      
    WITH vw AS  
     (  
        SELECT itmIDComp, itmID  
        FROM @t1  
      
        UNION ALL  
      
        SELECT itmIDComp, itmID  
        FROM @t2  
    )   
    ,r AS  
     (  
        SELECT t.itmID AS itmIDComp  
               , NULL AS itmID  
               ,CAST(0 AS bigint) AS N  
               ,1 AS Lvl  
        FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID)   
      
    UNION ALL  
      
    SELECT t.itmIDComp  
        , t.itmID  
        , ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N  
        , Lvl + 1  
    FROM r   
        JOIN vw AS t ON t.itmID = r.itmIDComp  
    )   
      
    SELECT Lvl, N FROM r;  
    

    Tuesday, January 14, 2020 11:08 AM
  • The error seems to be due to this part:

            ANS_CTE AS A1
        INNER JOIN
            ANS_CTE AS A2 ON A1.ANS_IndividualID = A2.ANS_IndividualID                             AND A1.rownum = A2.rownum + 1

    You are referring to the CTE in the query. You cannot do that.

    I think one of the occurrences of ANS_CTE should be dbo].[vCDCP_rptANSImprove].

    ...if this should be a recursive query at all. I am not sure what you are trying to achieve.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, January 14, 2020 10:37 PM