Oracle, сложный запрос, помогите.

[Dr_Jakero]

Добрый день, возник вопрос по написанию сложного запроса. Итак:

Существуют две таблицы ( fp_security_subject, fp_security_member) и вьюха (v_security_user). В таблицах достаточно большое количество записей.

v_security_user содержит информацию о пользователях:
ID | NAME | IDENT | EMP_ID
___________________________________ _________
1000006300| ULTRA | ULTRA | 1000005865
1200039438| TRAVINA | TRAVINA | 1200039406
1300000492| LARIONOV | LARIONOV | 1300000488
................................... ..................................


fp_security_subject содержит информацию о ролях в системе:
ID | NAME | TYPE_ID
___________________________________ ______
1200020992|Руководитель Бэк-офиса|2
1200020993|Специалист Бэк-офиса|2
1301365712|Кредитный анализ (просмотр)|2
................................... .......................

fp_security_member где указывается подчинение роли (причем указывается как подчинение сотрудника конкретной роли, так и подчинение между ролью и подролью)
GROUP_ID| MEMBER_ID
______________________
2| 1000006300
1| 1000006300
2| 1200002761
................................

Был написан запрос на для вывода пользователей с указанием их ролей

PHP код:

 select a.ID,
       a.NAME,
       a.IDENT,
       a.TYPE_ID,
       a.NOTE,
       b.memberOf,
       fp_pck_access.GetEmployeeFullName(a.emp_id) emp_name,
       fp_pck_access.GetUserStatusName(a.Name) status
  from v_security_user a,
       (select b.member_id, replace(concatenate(a.name),';','; ')  memberOf
          from fp_security_subject a, fp_security_member b
         where a.id = b.group_id
         group by b.member_id) b
 where a.ID = b.member_id(+) 


Что получилось

PHP код:

ID             NAME      IDENT       TYPE_ID      MEMBEROF                                               NAME                   STATUS
1000006300    ULTRA       ULTRA       1        Администраторы; Все пользователи                             Администратор - -    Активен
1200039438    TRAVINA     TRAVINA     1        тестовый пользователь    Все пользователи; Специалист ЗП    Травина Анастасия Александровна    Нет на сервере
1300000492    LARIONOV    LARIONOV    1        Все пользователи; Специалист ПЭБ                               Ларионов Алексей Викторович    Нет на сервере 


Вскоре появилась необходимость добавить в выборку еще один столбец строящийся по аналогии с MEMBEROF, но в него, нужно включать не только те подразделения в которые входит пользователь, но и их родительские подразделения.

Собственно и вопрос, имеется ли возможность это сделать в одном запросе или сделать это вообще с текущей структурой таблиц.
Ведь по идее, нужно во время выборки, для каждого подразделения в которое входит пользователь смотреть все подразделения в которое входи это подразделение и склеивать все в одну здоровую результирующую строку.

Заранее спасибо за ответы.

[Vapaamies]

Какова вложенность подразделений?

[Dr_Jakero]

Сообщение от Vapaamies

Какова вложенность подразделений?

Нет ограничений

[Stilet]

Я могу ошибаться, но думаю что вот такое придется городить: https://docs.oracle.com/html/E26088_01/queries003.htm

P.S. Сам я когда-то делал похожее, но на PL\SQL целой процедурой.

[Vapaamies]

Да, при неограниченной вложенности придется писать иерархический запрос с connect by/start with, а имя формировать функцией-оператором sys_connect_by_path.

[Dr_Jakero]

Спасибо, разобрался. Добился нужного результата, несколькими простыми запросами.

[Dr_Jakero]

Добрый день,
Все таки появилась необходимость написания одним запросом.
Написал, примерный запрос, который по идее должен выводить в правильном виде.
Вся суть этого запроса, в том что каждому юзеру назначены роли. Они выводятся в столбце memberOF. У каждой роли могут быть родители. И в столбце memberall нужно для каждого юзера вывести полный список до корневого родителя роли.

PHP код:

SELECT a.NAME,
       b.memberOf,
       c.memberall
FROM v_security_user a,
     (SELECT sm.member_id, replace(concatenate(ss.name),';','; ')  memberOf
      FROM fp_security_subject ss, fp_security_member sm
      WHERE ss.id = sm.group_id
      GROUP BY sm.member_id) b, --memberof
        
     (SELECT fsm.member_id, replace(concatenate(fss.name),';','; ')  memberall
      FROM fp_security_subject fss, fp_security_member fsm
      WHERE fss.id IN (SELECT d.id 
                       FROM v_security_user vsa,
                            (SELECT fsm.member_id, ffs.id
                             FROM fp_security_subject ffs, fp_security_member fsm 
                             WHERE ffs.id = fsm.group_id) d 
                       WHERE vsa.ID = a.ID And vsa.ID = d.member_id(+))      --memberall
      GROUP BY fsm.member_id) c   
WHERE a.ID = b.member_id 


Но тут выскакивает вполне законная ошибка, что во внутреннем запросе я использую a.ID из внешнего:
[Error] Execution (43: 39): ORA-00904: "A"."ID": недопустимый идентификатор

Вопрос, можно ли как-нибудь передать a.ID во внутренний запрос? Так как мне нужно, чтобы он выполнялся для каждой строки. Или нужно по другому строить запрос?

[Vapaamies]

Что такое concatenate?

[Dr_Jakero]

Ну это просто не системная процедура для склеивания в одну строку записей.
Пример:
Здесь пользователь имеет три роли: "Все пользователи;Риск-аналитик;Пользователь УРКБ" - выводится в столбец memberof
В свою очередь Роль "Пользователь УРКБ" имеет две родительские роли и в столбец memberall должно выводиться:
"Все пользователи;Риск-аналитик;Пользователь УРКБ; Модуль "Документооборот"; "АФСЗ"

[Vapaamies]

Сообщение от Dr_Jakero

В свою очередь Роль "Пользователь УРКБ" имеет две родительские роли

Как это две родительские роли? У вас там граф, что ли?