Click HERE to see how Saviynt Intelligence is transforming the industry. |
09/02/2024 02:56 AM
Hi,
I have a requirement where we need to restrict users in Access request without active managers. To achieve this, I am updating the end-user SAV role whom to request with an HQL statement. Below is the HQL statement that I am using:
select a from Users a where a.statuskey =1 and a.manager in (select id from Users where statuskey =1)
I want to optimize the above HQL, I tried using inner join, but it errors out and is not accepted in Saviynt, can you let me know if I can optimize the above HQL?
Please note: This is HQL and not SQL.
Regards,
Arpitha
09/02/2024 03:21 AM
Hi @anekkilady Can you try this?
select a from Users a where a.statuskey = 1 and a.manager in (select id from Users u where u.statuskey = 1)
Regards,
Indra
09/02/2024 03:31 AM
Thank you for your response @indra_hema_95. This is the same query that I have pasted and needs to be optimized.
Regards,
Arpitha
09/02/2024 03:35 AM
Hi @anekkilady There is a small difference in my query can you just copy and paste and see if it errors out or not?
Regards,
Indra
09/02/2024 03:54 AM - edited 09/02/2024 08:35 AM
Hi @anekkilady , try this
select a from Users a, Users aa where a.manager=aa.id and aa.statuskey=1 and a.statuskey=1
09/02/2024 06:25 AM
select u
from Users u
join Users m on u.manager = m.id
where u.statuskey = 1
and m.statuskey = 1
09/04/2024 03:08 AM
Thank you for your response.
@Amit_Malik The query given below is accepted however, I do not see any users when I try to place the access request for others.
select a from Users a, Users aa where a.manager=aa.id and aa.statuskey=1 and a.statuskey=1
@rushikeshvartak As mentioned in the note, join is not accepted, were you able to use this in your environment?
@indra_hema_95 I am using the query that you have provided and that is working fine. Thank you! Can we optimize this more?
Regards,
Arpitha N
09/04/2024 04:15 AM - edited 09/04/2024 04:17 AM
Hi @anekkilady ,
Internally Saviynt is only picking condition after where, it is not joining tables
select a from Users a, Users aa where a.manager=aa.id and aa.statuskey=1 and a.statuskey=1 becomes
REQ=select a from Users a where ( ( a.manager=aa.id and aa.statuskey=1 and a.statuskey=1 ) )} "
This is why the join (via comma) is not giving any results
select a from Users a where a.statuskey =1 and a.manager in (select id from Users where statuskey =1) becomes
REQ=select a from Users a where ( ( a.statuskey =1 and a.manager in (select id from Users where statuskey =1) ) )} "
I am afraid we can not optimize it to the extent you want looking at how the queries transforms behind the scene
09/04/2024 07:38 AM
Below query worked for me
select a from Users a where a.statuskey =1 and a.manager in (select mmmm.id from Users mmmm where mmmm.statuskey =1)