Oracle의 Connect By와 유사하게 Tree구현

WITH 
    h$cte AS (
        SELECT 1 AS LEVEL,
               A.*,
               dbo.xf_nvl_c( dbo.F_FRM_GET_TERM('KO', 'MENU_NM', a.MENU_ID), a.menu_nm ) AS title,
               dbo.F_FRM_IS_FAV_MENU(A.menu_id, 1) AS is_favorite,
               (SELECT b.OBJECT_DISPLAY_NM
                  FROM FRM_EXECUTABLE_OBJECT b
                 WHERE b.object_nm = a.object_id
                   AND b.company_cd = a.company_cd) AS object_nm,
               (SELECT attribute_value
                  FROM frm_object_attribute oa
                 WHERE oa.object_id =
                       (SELECT object_id
                          FROM frm_executable_object
                         WHERE object_nm = a.object_id
                           AND company_cd = a.company_cd)
                   AND oa.attribute_type_cd = 'ahthCheck'
                   AND oa.attribute_nm = 'pwdCheck') AS auth_check
          FROM FRM_MENU A
         WHERE A.USE_YN = 'Y'
           AND A.PARENT_MENU_ID = 'SYS_ADMIN'
         UNION ALL 
         SELECT h$cte.LEVEL + 1 AS LEVEL,
               A.*,
               dbo.xf_nvl_c( dbo.F_FRM_GET_TERM('KO', 'MENU_NM', a.MENU_ID), a.menu_nm ) AS title,
               dbo.F_FRM_IS_FAV_MENU(A.menu_id, 6639947) AS is_favorite,
               (SELECT b.OBJECT_DISPLAY_NM
                  FROM FRM_EXECUTABLE_OBJECT b
                 WHERE b.object_nm = a.object_id
                   AND b.company_cd = a.company_cd) AS object_nm,
               (SELECT attribute_value
                  FROM frm_object_attribute oa
                 WHERE oa.object_id =
                       (SELECT object_id
                          FROM frm_executable_object
                         WHERE object_nm = a.object_id
                           AND company_cd = a.company_cd)
                   AND oa.attribute_type_cd = 'ahthCheck'
                   AND oa.attribute_nm = 'pwdCheck') AS auth_check
          FROM FRM_MENU A, h$cte
         WHERE A.USE_YN = 'Y'
           AND h$cte.MENU_ID = A.PARENT_MENU_ID
  ) SELECT *
      FROM h$cte
     WHERE h$cte.USE_YN = 'Y' AND (h$cte.MENU_GROUP IS NULL OR h$cte.MENU_GROUP LIKE '%SYS_ADMIN%' OR h$cte.MENU_GROUP LIKE '%MAIN_LINK%')