Duplicates records in access validation

[devendranj (jayaraj devendran)]

Which IIQ version are you inquiring about?

Version 7.3

Share all details related to your problem, including any error messages you may have received.

I am getting duplicates entry when i run role based certifications. So i am trying to find where the duplicate entry are present. i am seeing only one entry in the SPT_IDENTITY_ENTITLEMENT

select * from SPT_IDENTITY_ENTITLEMENT where IDENTITY_ID in (select id from SPT_IDENTITY where NAME = ‘’) and VALUE like ‘%<ROLE_NAME%’

[iamksatish (Satish Kurasala)]

@devendranj
Can you be more clear, it is role assignment Certification and you are seeing duplicate roles for same identity within Certification but when you check the identity there is only one role tied to the user, can you provide the screenshot from certification view.

[devendranj (jayaraj devendran)]

yes @iamksatish you are right. Please find the screenshot.

[AdamVentum (Adam Waszak)]

@devendranj hi and hello,
To identify duplicate entries in the SPT_IDENTITY_ENTITLEMENT table, you can use the GROUP BY clause along with the COUNT() function to count occurrences of each entry, then filter for those with counts greater than one. Here’s how you can do it:

SELECT IDENTITY_ID, VALUE, COUNT() AS count
FROM SPT_IDENTITY_ENTITLEMENT
WHERE IDENTITY_ID IN (SELECT id FROM SPT_IDENTITY WHERE NAME = ‘’)
AND VALUE LIKE ‘%<ROLE_NAME%’
GROUP BY IDENTITY_ID, VALUE
HAVING COUNT() > 1;

This query will show you all the IDENTITY_ID and VALUE combinations where there are duplicate entries.

Regards,
Adam

[devendranj (jayaraj devendran)]

we are only finding one entry in the table

[vishal_kejriwal1 (Vishal Kejriwal)]

What type of review you are running ?

[devendranj (jayaraj devendran)]

Role based review. we are seeing duplicates only in the review.

[devendranj (jayaraj devendran)]

Any other suggestions?

[pravin_ranjan (Kumar Ranjan)]

@devendranj try by Exclusion Rule,

a. First See if there you will get duplicate items
b. if yes, then we need to use Set filterItems = new HashSet(); // Set will only maintain Unique items.

try some code like

`
if (certifiable instanceof Bundle) {
Bundle role = (Bundle) certifiable ;
String roleName = (String) role.getName();
// Print here roleName so see if any duplicate names here
}

`
Once you see duplicate names then it’s easy to handle duplicate case. You can clear the items and again add in items by items.addAll(filterItems);

Let’s meet again after your test.