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

Using HQL in the Advanced Rules config

RoniYla
New Contributor
New Contributor

Hi,

I'm trying to configure rules using the advanced config (HQL).

This is what I had before and it was working as intended:
a.employeeType= 'Employee' AND a.startdate < DATE_ADD(NOW(), INTERVAL 14 DAY) AND (a.enddate > NOW() OR a.enddate is null)

After our customer updated the environment it stopped functioning as SQL functioned stopped working there.

I was redirected here on how to do this config with HQL and asked to use the current_date - functionality.

Ex. where inv.dueDate < = current_date - 15

I had some issues with this and after doing some testing I noticed that using this query:
select current_date-15 I got this as the result:

RoniYla_0-1691151817162.png

Now this is an issue since there aren't that many (2023.07.89) days in a month so this can't be used reliably in the advanced config.

2 REPLIES 2

naveenss
All-Star
All-Star

Hi @RoniYla 

Use the below sample code snippet:

datediff (curdate(), a.startdate) between 0 and 14

Your advanced query should look something like below:

a.employeeType= 'Employee' AND datediff (curdate(), a.startdate) between 0 and 14 AND (a.enddate > NOW() OR a.enddate is null)

Let me know if this helps!

 

Regards,
Naveen Sakleshpur
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

rushikeshvartak
All-Star
All-Star

Refer : https://forums.saviynt.com/t5/identity-governance/how-to-configure-technical-rule-with-quot-startdat... 

a.statuskey = 1 and datediff (curdate(), a.startdate) between 0 and 15


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