Union in analytics query

[Sitarasmi]

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

[dgandhi · Accepted solution]

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')

[Sitarasmi]

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

[dgandhi · Accepted solution]

Try something like below:

[Sitarasmi]

Thank you for you quick support, its working now.