-1
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
Charlieface
  • 52,284
  • 6
  • 19
  • 43
simran kukreja
  • 3
  • 1
  • 2
    Great, thanks for telling us. Good luck. If you get stuck, please do [edit] your question to explain where exactly you are stuck, what you tried, and why it isn't working. You might want to read the [tour] too, as you are yet to do so; you seem to have mistaken [so] for a blogging site (it isn't one). – Thom A Apr 28 '22 at 14:10
  • 1
    There is no direct equivalent for the `START WITH ... CONNECT BY` construct in most other databases, including SQL Server. Does this help? https://stackoverflow.com/questions/2200636/oracle-connect-by-clause-equivalent-in-sql-server – pmdba Apr 28 '22 at 14:22

1 Answers1

1

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;
Charlieface
  • 52,284
  • 6
  • 19
  • 43