I need to export only the ‘user id’ and ‘group’ it belongs to from BO
Supervisor or
Fetch the records by SQL from the BO repository schema.
When we tried with method 1, we got other info also exported. Which we don’t want. For method 2, please give the sql if you have any, which is equilant to like
SQL> select username, usergroup from x,y
Where x.a=y.b
Group by usergroup
Can any one please advice how to achieve the above requirement?.
You need four tables to do this. Actually two tables and two aliases. All groups and users are stored in the OBJ_M_ACTOR table. Users have one type ID value and groups have another. To put users and groups together you need (if my memory is correct) OBJ_M_ACTORLINK. You will like OBJ_M_ACTOR where type ID = user to OBJ_M_ACTORLINK based on key values, then link OBJ_M_ACTOR where type ID = group to OBJ_M_ACTORLINK as well. Then the two copies of OBJ_M_ACTORLINK are linked together. I just don’t remember the exact column names used to do the linking.
There is a repository reference here on BOB that will probably help you do the actual SQL for the tables I’ve listed.
select
actor1.m_actor_c_name user_name
,actor2.m_actor_c_name group_name
from
obj_m_actor actor1
,obj_m_actor actor2
,obj_m_actorlink actorlink1
,obj_m_actorlink actorlink2
where
actorlink1.m_actl_n_actorid = actor1.m_actor_n_id
and actorlink2.m_actl_n_actorid = actor2.m_actor_n_id
and actorlink1.m_actl_n_fatlinkid = actorlink2.m_actl_n_id
and actorlink1.m_actl_n_lat != 1 /* only list users not sub-groups */
and actor1.m_actor_n_type = 16 /*user */
order by upper(actor1.m_actor_c_name), actor2.m_actor_c_name
(BOB member since 2002-06-06)