sql-serveroracle-databaseconnect-byrecursive-cte

I need to convert this query from Oracle to SQL Server


SELECT
    ffl2.fee_record_code,
    (SELECT max(fee_record_code)
     FROM fees_list ffl3 
     START WITH ffl3.fee_record_code = Nvl(ffl2.fes_associated_record, ffl2.fee_record_code)
     CONNECT BY PRIOR ffl3.fee_record_code = ffl3.fes_associated_record) final_record_code
FROM 
    fees_list ffl2

Solution

  • SQL Server does not have CONNECT BY. You need to use a recursive CTE.

    Unfortunately, you cannot put a recursive CTE into a subquery or derived table. So the easiest way to solve your particular problem is to use an inline Table Valued Function

    CREATE OR ALTER FUNCTION dbo.GetMaxRecords (@fee_record_code int)
    RETURNS TABLE
    AS RETURN
    
    WITH cte AS (
        SELECT fee_record_code
        FROM fees_list ffl3 
        WHERE ffl3.fee_record_code = @fee_record_code
    
        UNION ALL
    
        SELECT fee_record_code
        FROM fees_list ffl3
        JOIN cte ON cte.fee_record_code = ffl3.fes_associated_record
    ) 
    SELECT
      fee_record_code = MAX(cte.fee_record_code)
    FROM cte;
    
    go
    
    SELECT
        ffl2.fee_record_code,
        final_record_code = (
          SELECT r.fee_record_code
          FROM dbo.GetMaxRecords( ISNULL(ffl2.fes_associated_record, ffl2.fee_record_code) ) r
        )
    FROM 
        fees_list ffl2;