Hello, everyone,
I am using One Identity - Identity Manager v. 8.2.
I would like to create a View on the Database using the launchpad's 'Extend Schema' tool.
The query that generates the view is very complex and requires many JOINs. The tool requires that to create a View the first field must be a UID field and must be unique, but I could not find a field that was unique for all rows because of the many JOINs. I tried to add a unique field by starting the query with "SELECT newId() AS UID_View" (source: Stack Overflow. It works if I try the query directly on the DB), but the system rejected it saying that I must use a UID field.
Do you have any ideas on how to solve this problem? The query I would like to use is as follows:
SELECT a.cn, a.FirstName, a.LastName, p.CustomProperty02, fp.Ident_FirmPartner, ic.CCC_Name, p.CustomProperty01, pwo.DisplayOrg, p.ExitDate, a.AccountDisabled, paer.UID_Person FROM UNSAccountB a JOIN Person p ON a.UID_Person = p.UID_Person JOIN PersonWantsOrg pwo ON p.UID_Person = pwo.UID_PersonOrdered JOIN FirmPartner fp ON p.UID_FirmPartner = fp.UID_FirmPartner JOIN CCC_INWI_Companies ic ON p.CCC_UID_CCC_INWI_Companies = ic.UID_CCC_INWI_Companies JOIN TSB_VAERoleUnsContainerB tsb ON a.UID_UNSRootB = tsb.UID_UNSRootB JOIN PersonInAERole paer ON tsb.UID_AERoleMemberShip = paer.UID_AERole WHERE pwo.OrderState = 'Assigned' GROUP BY a.cn, a.FirstName, a.LastName, p.CustomProperty02, fp.Ident_FirmPartner, ic.CCC_Name, p.CustomProperty01, pwo.DisplayOrg, p.ExitDate, a.AccountDisabled, paer.UID_Person