Re-writting the query without "Connect By "

Question

I am rewriting the query to replace to remove CONNECT BY:

SELECT *
FROM   ADM_TRT AT
       INNER JOIN UTILISATEUR U
       ON U.UTI_ID = AT.UTI_ID
       INNER JOIN
       (
         SELECT CM.MAI_ID
         FROM   CON_MAI CM
         CONNECT BY CM.MAI_PER_RES = PRIOR CM.MAI_ID
         START WITH CM.MAI_ID IN (
                                   SELECT MAJ_ID
                                   FROM   DROIT_LOGIN
                                   WHERE LOG_ID = 21543
                                 )
       ) CON_MAI_FILTERED_ON_LOGIN
       ON AT.TRT_MAI_ID = CON_MAI_FILTERED_ON_LOGIN.MAI_ID;

For CONNECT BY Part , I wrote this

WITH tree (MAI_ID,MAI_PER_RES, level1) AS  (
  SELECT MAI_PER_RES, MAI_ID, 1 as level1 FROM CON_MAI
  UNION ALL
  SELECT child.MAI_ID, child.MAI_PER_RES, parent.level1 + 1
  FROM CON_MAI child --Line 20
       JOIN tree parent
       on parent.MAI_PER_RES = child.MAI_ID
) 
SELECT MAI_ID FROM tree

But I am stuck to integrate this in subquery in the CONNECT BY sub-query. Can someone please help to integrate this?

Answer 1

It looks like you have the recursion reversed in the recursive sub-query and can use:

WITH tree (MAI_ID) AS  (
  SELECT MAI_ID
  FROM   CON_MAI
  WHERE  MAI_ID IN ( SELECT MAJ_ID
                     FROM   DROIT_LOGIN
                     WHERE LOG_ID = 21543 )
UNION ALL
  SELECT c.MAI_ID
  FROM   CON_MAI c
         JOIN tree p
         on c.MAI_PER_RES = p.MAI_ID
)
SELECT *
FROM   ADM_TRT AT
       INNER JOIN UTILISATEUR U
       ON U.UTI_ID = AT.UTI_ID
       INNER JOIN tree CON_MAI_FILTERED_ON_LOGIN
       ON AT.TRT_MAI_ID = CON_MAI_FILTERED_ON_LOGIN.MAI_ID;

(untested as I do not have your tables or data)