I need to convert this query from Oracle to SQL Server-CodePudding

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

CodePudding user response：

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;