Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
04/26/2024 08:36 AM
Hi Team,
We are using below query to fetch account data from different entitlements and trying use union query. Its giving error.
Can anyone please check & guide what is wrong with this.
select a.NAME, a.DISPLAYNAME, a.COMMENTS as 'DN', a.STATUS as 'Account_Status', ev.ENTITLEMENT_VALUE from accounts a left join account_entitlements1 ae on a.ACCOUNTKEY = ae.ACCOUNTKEY left join entitlement_values ev on ae.ENTITLEMENT_VALUEKEY =ev.ENTITLEMENT_VALUEKEY left join entitlement_types et on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY left join endpoints e on et.ENDPOINTKEY = e.ENDPOINTKEY where e.ENDPOINTNAME in ( 'Active Directory', 'Active Directory Admin Accounts') and ev.ENTITLEMENT_VALUE in ( 'Group1', 'Group2', ) and a.STATUS in (1, 'Active')
union
select a.NAME, a.DISPLAYNAME, a.COMMENTS as 'DN', a.STATUS as 'Account_Status', ev.ENTITLEMENT_VALUE from accounts a left join account_entitlements1 ae on a.ACCOUNTKEY = ae.ACCOUNTKEY left join entitlement_values ev on ae.ENTITLEMENT_VALUEKEY =ev.ENTITLEMENT_VALUEKEY left join entitlement_types et on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY left join endpoints e on et.ENDPOINTKEY = e.ENDPOINTKEY where e.ENDPOINTNAME in ( 'Active Directory Admin Accounts') and a.DISPLANAME in ('Acc1', 'Acc2', 'Acc3') and a.STATUS in (1, 'Active')
Thanks,
Sitarasmi
Solved! Go to Solution.
04/26/2024 08:41 AM
Please try below:
select a.NAME, a.DISPLAYNAME, a.COMMENTS as 'DN', a.STATUS as 'Account_Status', ev.ENTITLEMENT_VALUE from accounts a left join account_entitlements1 ae on a.ACCOUNTKEY = ae.ACCOUNTKEY left join entitlement_values ev on ae.ENTITLEMENT_VALUEKEY =ev.ENTITLEMENT_VALUEKEY left join entitlement_types et on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY left join endpoints e on et.ENDPOINTKEY = e.ENDPOINTKEY where e.ENDPOINTNAME in ( 'Active Directory', 'Active Directory Admin Accounts') and ev.ENTITLEMENT_VALUE in ( 'Group1', 'Group2') and a.STATUS in (1, 'Active')
union
select a.NAME, a.DISPLAYNAME, a.COMMENTS as 'DN', a.STATUS as 'Account_Status', ev.ENTITLEMENT_VALUE from accounts a left join account_entitlements1 ae on a.ACCOUNTKEY = ae.ACCOUNTKEY left join entitlement_values ev on ae.ENTITLEMENT_VALUEKEY =ev.ENTITLEMENT_VALUEKEY left join entitlement_types et on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY left join endpoints e on et.ENDPOINTKEY = e.ENDPOINTKEY where e.ENDPOINTNAME in ( 'Active Directory Admin Accounts') and a.DISPLANAME in ('Acc1', 'Acc2', 'Acc3') and a.STATUS in (1, 'Active')
04/26/2024 08:48 AM
Thank you so much for quick support.
Can i just ask one more query - the second query (after Union) returns the accounts with all entitlements, We just want to fetch the acocunt name through second one.
Please suggest if this is possible.
Thanks,
Sitarasmi
04/26/2024 08:58 AM
Try something like below:
04/28/2024 11:16 PM
Thank you for you quick support, its working now.