Query for usermanager certification for the managers to certify the role owners

[manojarisetti]

Hi,

I'm using the User manager Campaign for the managers to certify the Roleowner's access to the role. I'm enabling the access verification only(Step2). 

Need to restrict the condition in Advance campaign configurations as:
1. Users Query : Should filter the role owners (I.e., primary certifiers) of the role who is assigned to their respective role.
2. Entitlements Query: Should filter the Entitlements in certification only of the respective role assigned as a primary certifier.

We are able to write the query which filters the primary certifiers assigned to their respective roles, But the query is not working while trying in User manager certification.

The Manager should be able to certify only the roleowner's respective entitlement of the role.

Query working in analytics/ Data analyzer:

SELECT DISTINCT ro.rolekey AS RoleID, ro.role_name AS RoleName, rou.USERKEY AS RoleOwnerID, u.USERNAME AS RoleOwnerName FROM roles ro JOIN role_owners rou ON ro.rolekey = rou.ROLEKEY JOIN role_user_account rua ON rua.userkey = rou.userkey JOIN users u ON u.userkey = rua.userkey WHERE u.USERNAME IN ( SELECT u2.USERNAME FROM role_user_account rua2 JOIN users u2 ON u2.userkey = rua2.userkey WHERE rua2.ROLEKEY = ro.rolekey  and  rou .RANK = '26' ) ORDER BY ro.rolekey, u.USERNAME;

Could you advise on the possible filters queries to make this work. 

Thanks,
Manoj
 

[NM]

Hi @manojarisetti transform your query and rely on userkey or username.

Eg 

Userkey in (subquery to get the role owners)

[manojarisetti]

Hi @NM ,

We already tried the query with Userkey and Username. It still shows "Invalid SQL syntax error".

Thanks,
Manoj

[NM]

@manojarisetti share the query.

[manojarisetti]

Userkey:

u.userkey IN (SELECT u.USERKEY FROM roles ro JOIN role_owners rou ON ro.rolekey = rou.rolekey JOIN role_user_account rua ON rua.userkey = rou.userkey JOIN users u ON u.userkey = rua.userkey WHERE u.username IN ( SELECT u2.username FROM role_user_account rua2 JOIN users u2 ON u2.userkey = rua2.userkey WHERE rua2.rolekey = ro.rolekey and rou .RANK = '26' ) ORDER BY ro.rolekey, u.username)

Username:

u.username IN (SELECT u.username FROM roles ro JOIN role_owners rou ON ro.rolekey = rou.rolekey JOIN role_user_account rua ON rua.userkey = rou.userkey JOIN users u ON u.userkey = rua.userkey WHERE u.username IN ( SELECT u2.username FROM role_user_account rua2 JOIN users u2 ON u2.userkey = rua2.userkey WHERE rua2.rolekey = ro.rolekey and rou .RANK = '26' ) ORDER BY ro.rolekey, u.username)

[NM]

@manojarisetti try without alias 

userkey IN (SELECT u.USERKEY FROM roles ro JOIN role_owners rou ON ro.rolekey = rou.rolekey JOIN role_user_account rua ON rua.userkey = rou.userkey JOIN users u ON u.userkey = rua.userkey WHERE u.username IN ( SELECT u2.username FROM role_user_account rua2 JOIN users u2 ON u2.userkey = rua2.userkey WHERE rua2.rolekey = ro.rolekey and rou .RANK = '26' ) ORDER BY ro.rolekey, u.username)

And I hope it is working from data analyzer.