GaramX

이 문서는 읽기 전용입니다. 원본을 볼 수는 있지만 바꿀 수는 없습니다. 문제가 있다고 생각하면 관리자에게 문의하세요.
====== Oracle의 Connect By와 유사하게 Tree구현 ======
<code sql>
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%')
</code>
GaramX

사용자 도구

사이트 도구

문서 도구
