Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Custom query into Enhanced Query

MichaelSchmid
New Contributor II
New Contributor II

I'm trying to change the format of a deprecated custom query, which is quiet important for us, into the format of an enhanced query. Can someone please help at the following query?
Both Update statements were in one query, is it possible to have them together in one enhanced query again?

Thank you and BR, Michael

 

UPDATE users u
SET u.customproperty35 =
(SELECT GROUP_CONCAT(DISTINCT user_groups.user_groupname ORDER BY user_groups.user_groupname ASC SEPARATOR ' ') AS 'Local Mitigating Controls Groups'
FROM usergroup_users, user_groups
WHERE user_groups.usergroupkey = usergroup_users.user_groupkey
AND usergroup_users.userkey = u.userkey )
WHERE u.userkey IN (SELECT userkey FROM usergroup_users, user_groups WHERE usergroup_users.user_groupkey = user_groups.usergroupkey AND (user_groupname LIKE 'ValueA%'));

UPDATE users u
SET customproperty36 =
(select * from (select GROUP_CONCAT(uu.username ORDER BY uu.username ASC SEPARATOR ',') AS 'KeyUsersUsernamelList'
from usergroup_users ugu, user_groups ug, users uu
WHERE uu.userkey = ugu.userkey
and ugu.user_groupkey = ug.usergroupkey
AND ug.user_groupname = 'ValueA') as subResult)
where u.customproperty10 = ValueB;

2 REPLIES 2

PremMahadikar
Valued Contributor
Valued Contributor

Hi @MichaelSchmid ,

I see you query is updating users' table. But 'Enhanced query execution' job doesn't work on user table.

A workaround would be to use the below;

  • Use inline preprocessor json
  • Sav 4 Sav update user json which can be triggered using user-update rule
  • Extensible jar

If this helps your question, please consider selection Accept As Solution and hit Kudos

rushikeshvartak
All-Star
All-Star

Enhanced query is not supported on users table.

If this is existing custom query then it will work as expected. if there are any changes or new integration then use alternatives suggested by @PremMahadikar 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.