BusinessObjects Board

Oracle SQL to display Users and Groups

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)