Optimizing the HQL statement in SAV Role

[anekkilady]

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 

[indra_hema_95]

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

[anekkilady]

Thank you for your response @indra_hema_95. This is the same query that I have pasted and needs to be optimized.

Regards,

Arpitha 

[indra_hema_95]

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

[Amit_Malik]

Hi @anekkilady , try this 

select a from Users a, Users aa where a.manager=aa.id and aa.statuskey=1 and a.statuskey=1

[rushikeshvartak]

select u
from Users u
join Users m on u.manager = m.id
where u.statuskey = 1
and m.statuskey = 1

[anekkilady]

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

[Amit_Malik]

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

[rushikeshvartak]

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)