Hi.The group names are an Object in the OH table. USRG binds users to (potentially more than one) group identifiers, USR maps those group identifiers to a unique ID (or vice versa :) and that unique ID is the group name in OH.Here is a thread that has asked almost the same question:https://community.broadcom.com/enterprisesoftware/communities/community-home/digestviewer/viewthread?GroupId=1435&MID=801626&CommunityKey=2e1b01c9-f310-4635-829f-aead2f6587c4&tab=digestviewerI tried the solution from that thread on my Oracle DB, works (even though I'm not 100% sure I understand how). This gives you account names and their user groups, and obviously, if an account has multiple groups, he is output multiple times.
That's only account names however. One can link that to USR probably by joining "OH.OH_IDNR on USR.USR_OH_IDNR" to include the data from the USR table. But I'm sorry, I just tried this for a brief time and I couldn't get it to work right away, I'm also no SQL query expert either. I'd need to invest more time into this which I don't have right now. Does your company possibly have a DBA or sql expert who can help with contructing that join statement?Best regards,
select a.OH_NAME, b.OH_Name, USR_FirstName,USR_LastName
a.OH_IDNR = USRG_USR_Idnr and
b.OH_Idnr = USRG_USG_Idnr and
order by 1
select a.OH_Client as "Client", a.OH_NAME as "User Name", USR_FIRSTNAME, USR_LASTNAME, USR_EMAIL1,b.OH_Name as "USRG Name" from OH a, oh b, USRG, USRwhere a.OH_IDNR = USR_OH_IDNRand a.OH_CLIENT not in (0)and b.OH_CLIENT not in (0)and a.OH_IDNR = USRG_USR_Idnrand b.OH_Idnr = USRG_USG_Idnrand a.OH_DELETEFLAG = 0and b.OH_DELETEFLAG = 0order by 1,3;