지난 번에 배운 계층 쿼리를 활용해서, 이번에는 사용자별 Role 에 따른 접근 목록을 가져오는 쿼리를 작성 중입니다.

일주일 넘게 삽질하고 있지만, 원하는 결과를 못만들고 있어, 질문 올려봅니다.

원하는 결과는, 사용자마다 한개 이상의 Role 이 지정될 수 있기 때문에,

계층 접근 목록을 Role 마다 나눠서 보여주려고 합니다.

현재 제 쿼리는 계층 중간 중간에 섞여서 나오고 있습니다.

WITH usr(usr_id, usr_kr, usr_sts) AS
    (
        SELECT 115, 'John', 1 FROM dual UNION ALL
        SELECT 150, 'Kole', 1 FROM dual UNION ALL
        SELECT 200, 'Tony', 0 FROM dual
    ),
     test_elm(test_elm_id, test_parent_elm_id) AS
    (
        SELECT 1,  NULL FROM dual UNION ALL
        SELECT 100,  1 FROM dual UNION ALL
        SELECT 101,  1 FROM dual UNION ALL
        SELECT 102,  1 FROM dual UNION ALL
        SELECT 2,  NULL FROM dual UNION ALL
        SELECT 200,  2 FROM dual UNION ALL
        SELECT 201,  2 FROM dual UNION ALL
        SELECT 202,  2 FROM dual 
    ),
    usr_rl(usr_id, rl_id) AS
    (
        SELECT 115, 8 FROM dual UNION ALL
        SELECT 115, 9 FROM dual UNION ALL
        SELECT 150, 9 FROM dual UNION ALL
        SELECT 200, 7 FROM dual
    ),
    test_elm_rl(test_elm_id, rl_id, sel_checked, ins_checked, updt_checked, dlt_checked) AS
    (
        SELECT   1, 8, 1, 1, 1, 1 FROM dual UNION ALL   --parent
        SELECT   2, 8, 1, 1, 1, 1 FROM dual UNION ALL   --parent
        SELECT 102, 8, 0, 0, 0, 0 FROM dual UNION ALL   --child
        SELECT 200, 8, 0, 0, 0, 0 FROM dual UNION ALL   --child
        SELECT   1, 9, 1, 1, 1, 1 FROM dual             --parent
    )
SELECT *
    FROM (SELECT a.usr_id, a.usr_kr, b.test_parent_elm_id, b.test_elm_id
            , c.rl_id
            , SIGN(d.sel_checked + d.ins_checked + d.updt_checked + d.dlt_checked) AS checked
            , 'Role' AS Per_Grp
            FROM 
                usr a CROSS JOIN test_elm b     -- returns 24 rows (= 3 users x 8 elements )
                LEFT OUTER JOIN usr_rl c
                    ON a.usr_id = c.usr_id      -- returns 32 rows (= 16 elements from one user and 16 elements from two users )
                LEFT OUTER JOIN test_elm_rl d
                    ON c.rl_id = d.rl_id
                        AND b.test_elm_id = d.test_elm_id
                WHERE a.usr_sts = 1 
                ORDER BY a.usr_id, b.test_elm_id
)
START WITH test_parent_elm_id IS NULL
            AND checked = 1
CONNECT BY  prior usr_id = usr_id
        AND prior test_elm_id = test_parent_elm_id
        AND NVL(checked, 1) != 0
ORDER SIBLINGS BY usr_id, test_elm_id