Whom to request from advance query SAV role

[BG_IAM]

Hello,

I'm encountering an issue with an advanced query in the SAV role. I need to determine whom to request for the following criteria in JSON format:

1. For managers where the employeeType is either BOT or External.
AND
2. For the manager's manager where the username starts with 'Guest'.
The query I'm using isn't producing the expected results. Any ideas on how to address this scenario?

[{    "for": "UpdateUserRequest",    "query": "SELECT a FROM Users a WHERE (SELECT DISTINCT a.userkey 
 FROM Users a 
 WHERE a.manager = '${users.id}' 
   AND a.employeeType IN ('BOT','External')
UNION ALL SELECT ua.userkey 
 FROM Users ua 
 JOIN Users ua1 ON ua1.userkey = ua.manager 
 JOIN Users ua2 ON ua2.userkey = ua1.manager 
 WHERE ua.username LIKE 'Guest%' 
   AND ua2.userkey = '${users.id}')"}]

thx 

[CR]

this query working in Data analyzer ? u want result in user update request page? or requets page?

[PremMahadikar · Accepted solution]

Hi @BG_IAM ,

Union will not work here.

I have changed the query and its working for me. Please try it.

[{"for": "UpdateUserRequest","query":"select a from Users a where (a.employeeType IN ('BOT','External') or a.employeeType like 'Guest%') AND (a.manager='${users.id}' or a.manager in (select distinct a from Users a where a.manager = '${users.id}'))"}]

If this reply answers your question, please consider selecting Accept As Solution and hit kudos.

[rushikeshvartak]

SELECT a FROM Users a
WHERE (
SELECT DISTINCT a.userkey
FROM Users a1
WHERE a1.manager = '${users.id}'
AND a1.employeeType IN ('BOT','External')
UNION ALL
SELECT ua.userkey
FROM Users ua
JOIN Users ua1 ON ua1.userkey = ua.manager
JOIN Users ua2 ON ua2.userkey = ua1.manager
WHERE ua.username LIKE 'Guest%'
AND ua2.userkey = '${users.id}'
)