Oracle Recursive Query in a History table

Question

I want to do a recursive query in User History Table. I tried with the below query its giving only one record. Since its a history table there will be multiple records for particular user with Effective date.

 WITH superVis(EMPLID,CH_SUPV_ID) AS ( 
     SELECT A.EMPLID,A.CH_SUPV_ID
     FROM PSOFTUDB.PS_CH_RPTS_TO_TBL A WHERE A.EMPLID = @Param AND A.EFFDT = (select MAX(A2.EFFDT) from PSOFTUDB.PS_CH_RPTS_TO_TBL A2 WHERE A2.EMPLID = A.EMPLID)
     UNION ALL 
     SELECT e.EMPLID,e.CH_SUPV_ID FROM 
     PSOFTUDB.PS_CH_RPTS_TO_TBL e,superVis o where 
     o.CH_SUPV_ID = e.EMPLID 
     ) select * from superVis;

i want hierarchy of user with the respected manager. I was able to acheive this in DB2. When changing it to oracle am getting error.

Latest effective date of the current manager is the data to be used in the query.

Sample Data :

EMPLID  CH_SUPV_ID EFFDT
1844854 1730352 2020-12-03 00:00:00
1730352 1461958 2018-12-17 00:00:00
1461958 246001  2014-02-04 00:00:00
1461958 954507  2021-06-10 00:00:00
246001  123345  1999-04-12 00:00:00
246001  17139   1999-08-23 00:00:00
246001  18382   2004-04-13 00:00:00
246001  18442   2007-07-27 00:00:00
954507  971510  2019-05-01 00:00:00

Expected Output:

EMLID   CH_SUPV_ID EFFDAT
1844854 1730352 2020-12-03 00:00:00
1730352 1461958 2018-12-17 00:00:00
1461958 954507  2021-06-10 00:00:00
954507  971510  2019-05-01 00:00:00

Answer 1

First, deal with effective dates. This is what I did in the "temp" CTE. It shows max effective date for each emplid.

Having done that, you can easily get what you need using oracle's hierarchical queries feature.

with temp as (
  select EMPLID, CH_SUPV_ID, EFFDT, max(effdt) over(partition by emplid) maxdate
    from PS_CH_RPTS_TO_TBL
)
select * 
  from temp
connect by nocycle EMPLID = prior CH_SUPV_ID and effdt = maxdate
start with emplid = 1844854