Count mismatch(Huge Difference)

[gagan94]

Dear All,

I am getting loss of data post appending the role name as well in my recon report, on adding only the entitlements it is showing 553000, moment on adding role name as well count get shrink to 5534, This is a very huge difference I am getting, Can anybody help me out what is causing issue that count is reducing to least.

Below is the latest query which is regarding role addition, mapping is correct because data is coming but issue with the count. I am not having idea on left joins which table needs to be added and where.

select ep.endpointname as 'Endpoint_Name',r.ROLE_NAME as 'ROLENAME',CASE WHEN ev.ENTITLEMENT_VALUE is null THEN(select ev.DISPLAYNAME from entitlement_values ev) ELSE ev.ENTITLEMENT_VALUE end as 'Entitlemenet name',u.username as 'USERNAME',u.displayname as 'DISPLAYNAME',CASE U.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,acc.NAME as 'AccountName' ,(select max(updatedate) from arstasks where accountkey=ru.accountkey and ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY) as RoleGrantDate from users u,accounts acc,user_accounts ua,entitlement_values ev,endpoints ep,roles r,role_entitlements re,role_user_account ru,entitlement_types et where acc.accountkey=ua.accountkey and ua.userkey=u.userkey and acc.endpointkey=ep.endpointkey and re.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY and re.rolekey=r.rolekey and r.rolekey=ru.rolekey and ru.accountkey=ua.accountkey and ua.userkey=u.userkey and ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and et.endpointkey=ep.endpointkey and ru.accountkey=ua.accountkey and ua.accountkey=acc.accountkey and ru.accountkey=ua.accountkey and acc.status=1 and ep.endpointname=''

select ep.endpointname as 'Endpoint_Name',CASE WHEN ev.ENTITLEMENT_VALUE is null THEN(select ev.DISPLAYNAME from entitlement_values ev) ELSE ev.ENTITLEMENT_VALUE end as 'Entitlemenet name',u.username as 'USERNAME',u.displayname as 'DISPLAYNAME',CASE U.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,acc.NAME as 'AccountName' ,(select updatedate from arstasks where taskkey=ae.ARSTASKKEY) as RoleGrantDate from users u,accounts acc,user_accounts ua,account_entitlements1 ae,entitlement_values ev,endpoints ep where acc.accountkey=ua.accountkey and ua.userkey=u.userkey and ae.accountkey=acc.accountkey and ae.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY and acc.endpointkey=ep.endpointkey and acc.status=1 and ep.endpointname='' and u.statuskey=1

[rushikeshvartak]

Use left join

[gagan94]

I tried to add some outer joins as well, My query is getting errored out. Can you please help me out with this, I have rarely used outer joins used join condition like this. Below is the query

select distinct u.username,u.displayname as 'DISPLAYNAME',CASE u.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,acc.NAME as 'AccountName',ep.endpointname as 'Endpointname',r. ROLE_NAME,ev.ENTITLEMENT_VALUE from users u,roles r,entitlement_values ev,role_entitlements re,role_user_account ru,user_accounts ua,endpoints ep,entitlement_types et,accounts acc where re.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY and re.rolekey=r.rolekey and r.rolekey=ru.rolekey and ru.accountkey=ua.accountkey and ua.userkey=u.userkey and ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and et.endpointkey=ep.endpointkey and ru.accountkey=ua.accountkey and ua.accountkey=acc.accountkey and acc.status IN ( '1', 'Manually Provisioned', 'Active' )

I request your cooperation.

[rushikeshvartak]

select u.username,u.displayname,CASE u.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' ELSE 'Blank' END AS USERSTATUS,a.name as accountname,e.endpointname,(select role_name from roles where rolekey in (ae.assignedfromrole))role_name from account_entitlements1 ae join accounts a on a.accountkey=ae.accountkey join endpoints e on e.endpointkey=a.endpointkey left join user_Accounts ua on ua.accountkey = a.accountkey left join users u on u.userkey=ua.userkey

[sundas7]

Hi ,

Please let us know if there is any specific error..I tried running the above query in our dev..It seemed to run through fine.

Thanks

Shyam

[gagan94]

Hey Rushikesh,

I tried your same query to run and add some more fields still getting same results, after removing mapping it is showinh 'NO DATA FOUND'

select distinct u.username,u.displayname as 'DISPLAYNAME',CASE u.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,acc.NAME as 'AccountName',ep.endpointname as 'Endpointname',(select role_name from roles where rolekey in (ae.assignedfromrole))role_name,ev.ENTITLEMENT_VALUE,(select updatedate from arstasks where taskkey=ae.ARSTASKKEY) as RoleGrantDate from users u,roles r,entitlement_values ev,role_entitlements re,role_user_account ru,user_accounts ua,endpoints ep,entitlement_types et,accounts acc,account_entitlements1 ae where re.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY and re.rolekey=r.rolekey and r.rolekey=ru.rolekey and ru.accountkey=ua.accountkey and ep.endpointkey=acc.endpointkey and acc.accountkey=ae.accountkey and ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and et.endpointkey=ep.endpointkey and ru.accountkey=ua.accountkey left join user_Accounts ua on ua.accountkey=acc.accountkey left join users u on ua.userkey=u.userkey and r.ROLE_NAME=''

Could you please check once above query.

[rushikeshvartak]

query provided in another thread