Oracle用户历史表递归查询报错、仅返回单条记录问题求助

• 递归查询的子查询分支未过滤上级经理的最新生效日期，导致递归逻辑匹配异常，仅返回单条结果
• Oracle绑定变量使用:前缀，不支持原DB2的@变量前缀
• 原CTE未定义EFFDT字段，无法输出该字段

WITH superVis(EMPLID, CH_SUPV_ID, EFFDT) AS (
    SELECT A.EMPLID, A.CH_SUPV_ID, A.EFFDT
    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, e.EFFDT 
    FROM PSOFTUDB.PS_CH_RPTS_TO_TBL e
    JOIN superVis o ON o.CH_SUPV_ID = e.EMPLID
    WHERE e.EFFDT = (SELECT MAX(e2.EFFDT) 
                     FROM PSOFTUDB.PS_CH_RPTS_TO_TBL e2 
                     WHERE e2.EMPLID = e.EMPLID)
)
SELECT EMPLID, CH_SUPV_ID, EFFDT FROM superVis;

SELECT EMPLID, CH_SUPV_ID, EFFDT
FROM (
    SELECT EMPLID, CH_SUPV_ID, EFFDT,
           ROW_NUMBER() OVER (PARTITION BY EMPLID ORDER BY EFFDT DESC) AS RN
    FROM PSOFTUDB.PS_CH_RPTS_TO_TBL
) T 
WHERE RN = 1
CONNECT BY PRIOR CH_SUPV_ID = EMPLID
START WITH EMPLID = :Param;

输入参数:Param为1844854时，返回结果和预期输出完全一致，会按层级返回该员工到最高级经理的所有最新生效汇报关系。

内容的提问来源于stack exchange，提问作者Pradeep