Hi Team,
We have a requirement where the logged-in user should be able to view the Analytics Report, which will consist of data including user details for users who are part of an organization where the logged-in user is an Organization Owner.
The above requirement query has been configured; however, we need an enhancement to check if the logged-in user has Application A (suppose endpointkey=41) and if in Application A, the customproperty1 equals 'ABC_XYZ'. If this condition is met, the analytics should be created through the Utility, which is already in place.
I have tried to merge the above query with customproperty1 details; however, when I tried, I was not able to fetch the total records. The query was fetching details in such a way that only users with customproperty1 as 'ABC_XYZ' were appearing in the result. The query was not checking the logged-in user's Application A customproperty1.
The Existing Query:
SELECT u.username as USER_ID,
u.firstname as FIRST_NAME,
u.lastname as LAST_NAME,
u.email as EMAIL,
u.phonenumber as PHONENUMBER,
u.enddate as ENDDATE,
u.owner as MANAGER,
c.customername as ORGANIZATION_ID,
c.displayname as ORGANIZATION_NAME,
u.customproperty4 as POSTAL_CODE,
u.customproperty5 as MOBILE_NUMBER,
(select distinct dv.ATTRIBUTE2 from dataset_values dv where dv.DATASETNAME = 'Supported_Language' and dv.ATTRIBUTE1 = u.customproperty1) as PREFERRED_LANGUAGE,
u.customproperty2 as USER_BRANDS, u.comments as USERCOMMENTS FROM users u JOIN customer_users cu on cu.userkey=u.userkey JOIN customer c on cu.customerkey=c.customerkey where c.customerkey IN (select customerkey from organization_owners where userkey=${user.id})
Enhance Query with wrong output:
SELECT u.username as USER_ID,
u.firstname as FIRST_NAME,
u.lastname as LAST_NAME,
u.email as EMAIL,
u.phonenumber as PHONENUMBER,
u.enddate as ENDDATE,
u.owner as MANAGER,
c.customername as ORGANIZATION_ID,
c.displayname as ORGANIZATION_NAME,
u.customproperty4 as POSTAL_CODE,
u.customproperty5 as MOBILE_NUMBER,
(select distinct dv.ATTRIBUTE2 from dataset_values dv where dv.DATASETNAME = 'Supported_Language' and dv.ATTRIBUTE1 = u.customproperty1) as PREFERRED_LANGUAGE,
u.customproperty2 as USER_BRANDS, u.comments as USERCOMMENTS FROM users u JOIN customer_users cu on cu.userkey=u.userkey JOIN customer c on cu.customerkey=c.customerkey Join user_accounts ua on ua.userkey=u.userkey Join accounts a on a.accountkey=ua.accountkey where c.customerkey IN (select customerkey from organization_owners where userkey=${user.id}) and a.ENDPOINTKEY=41 and a.customproperty1='ABC_XYZ'
