Author: DCMatt
-- Search a repository for user and groups
-- 1. Change the act.m_actor_n_type as needed.
-- 2. Substitue <Top Level Group Name> as appropriate.
-- EXPECTED RESULTS: A rendering of the requested Groups and Users.
SELECT
LPAD(' ',(level-1)*5,' ')
||DECODE(act.m_actor_n_type, 1, 'GROUP: ', NULL)
||act.m_actor_c_name
FROM
obj_m_actorlink lnk,
obj_m_actor act
WHERE
act.m_actor_n_id = lnk.m_actl_n_actorid
AND act.m_actor_n_type IN (1,16) --Examples: 1=Group, 4=Supervisor, 16=User...
START WITH act.m_actor_c_name = '<Top Level Group Name>'
CONNECT BY PRIOR lnk.m_actl_n_id = lnk.m_actl_n_fatlinkid
ORDER SIBLINGS BY act.m_actor_c_name
Bob (BOB member since 2002-06-06)