Version 7.3
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%’
@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 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
What type of review you are running ?
Role based review. we are seeing duplicates only in the review.
