Error in report retrieving users without a sav role

[sbiswal]

Hello,

I've been working on a report to find out users that do not have a particular sav role.

The query is as follows:

select u.username as 'EmployeeID', u.systemusername as 'ADS ID' from users u where u.STATUSKEY = 1 and u.username NOT IN (select u1.username from users u1, user_savroles usav, savroles sav where u1.id = usav.USERKEY and usav.rolekey = sav.id and sav.authority = 'ROLE_GBT_EMPLOYEE')

I cannot find anything off in the query, but it is not giving me results. Does anyone have any inputs on this?

Thanks,

Seemran

[avinashchhetri]

Seemran,

I would suggest to use the Data Analyzer to validate the column names for the table.

select u.username as 'EmployeeID', u.systemusername as 'ADS ID' from users u
where u.STATUSKEY = 1 and u.username NOT IN (select u1.username from users u1,
user_savroles usav, savroles sav where u1.userkey = usav.USERKEY and
usav.rolekey = sav.ROLEKEY
and sav.ROLENAME = 'ROLE_GBT_EMPLOYEE')

Note: I did not check if the query is returning the correct results or not but just updated the correct column names.

Regards,

Avinash Chhetri

[rushikeshvartak · Accepted solution]

select u.username as 'EmployeeID', u.systemusername as 'ADS ID' from users u
where u.STATUSKEY = 1 and u.username NOT IN (select u1.username from users u1,
user_savroles usav, savroles sav where u1.userkey = usav.USERKEY and
usav.rolekey = sav.ROLEKEY
and sav.ROLENAME = 'ROLE_GBT_EMPLOYEE')