Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
05/22/2024 01:30 AM
Hi Team,
I used the previous tickets as reference to make an advanced query for a user update rule.
However, this is not working. The objective is to achieve this condition - term+1<=sysdate().
a.statuskey = 0 AND (##a.statuskey is updated##) AND (date_add(a.enddate, INTERVAL 1 DAY) <= CURDATE())
I tried with the ## values as well, however the query is not working. Please assist.
Regards,
Aarthi Anand
Solved! Go to Solution.
05/22/2024 03:20 AM
Hi @aarthianand93 ,
This is your current query -
a.statuskey = 0 AND (##a.statuskey is updated##) AND (date_add(a.enddate, INTERVAL 1 DAY) <= CURDATE())
The correct format is (##a.statuskey isupdated##) without space. Please try!
I would also suggest, split the query and try it: For example:
If this helps your question, please consider selecting Accept As Solution and hit Kudos
05/22/2024 03:36 AM
Hi @PremMahadikar,
I have tried with the query by removing the space. But it is saying invalid condition.
Please let me know if there is anything else that has to be modified. I did some trial and error but it's not working. Thank you.
a.statuskey = 0 AND (##a.statuskey isupdated##) AND (date_add(a.enddate, INTERVAL 1 DAY) <= CURDATE())
Regards,
Aarthi Anand
05/22/2024 08:27 PM - edited 05/22/2024 08:31 PM
Hi Team,
We have tried in older version 5.5 with the same query and it is validating the condition.
However, in 24.2 version the same query says invalid condition.
Advanced Query for Condition {termdate+1<=curdate()} -
a.statuskey = 0 AND ##a.statuskey isupdated## AND date_add(a.enddate, INTERVAL 1 DAY) <= CURDATE()
Please let us know if we can modify this query in anyway to make it work in 24.2 version.
Regards,
Aarthi Anand
05/22/2024 09:16 PM
Please share logs and rule screenshot.
05/22/2024 09:24 PM
05/22/2024 09:30 PM
date_add is not working in 24.x version, even though it works in data analyzer.
Error: Unexpected token
Check the below: a.statuskey = 0 AND ##a.statuskey isupdated## AND a.enddate, <= CURDATE()
The above should work fine.
For date_add, please raise a bug on freshdesk.
If this helps your question, please consider selecting Accept As Solution and hit Kudos
05/22/2024 09:33 PM
a.statuskey = 0 AND ##a.statuskey isupdated## AND a.enddate <= CURDATE()
@PremMahadikar typo comma fixed
05/22/2024 09:50 PM
Hi @PremMahadikar,
I tried this query which you provided.
a.statuskey = 0 AND ##a.statuskey isupdated## AND a.enddate <= CURDATE()
The isupdated condition is not working. If we remove this condition it is working. Is there anyway we can add the isupdated condition?
Regards,
Aarthi Anand
05/22/2024 09:53 PM
05/22/2024 10:04 PM
Both. Preview says Invalid Condition. We updated a user and the Rule Run shows as blank. The rule is not previewable or triggered if we add the isupdated condition.
Regards,
Aarthi Anand
05/22/2024 10:08 PM
Please share logs
05/22/2024 10:16 PM - edited 05/22/2024 11:57 PM
@rushikeshvartak @PremMahadikar
Attached are the logs.
05/22/2024 10:29 PM
fix below error in rule.
"2024-05-23T05:13:45.111+00:00","ecm-worker","hql.PARSER","quartzScheduler_Worker-2-8dx6d","ERROR","line 1:124: unexpected token: AN"
"2024-05-23T05:13:45.131+00:00","ecm-worker","changeaction.UserChangeActionService","quartzScheduler_Worker-2-8dx6d","DEBUG","error in User update rule executing qry:"
"2024-05-23T05:13:45.631+00:00","ecm-worker","","null-8dx6d","","org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: AN near line 1, column 124 [select a.id from com.saviynt.ecm.identitywarehouse.domain.Users a where a.id=16 AND ( 1 = 1 AND a.customproperty2 in('('AN','FW','CD','WE','SF','SW','WF','WW')') AND a.statuskey = '1' AND 1=1 )]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: AN near line 1, column 124 [select a.id from com.saviynt.ecm.identitywarehouse.domain.Users a where a.id=16 AND ( 1 = 1 AND a.customproperty2 in('('AN','FW','CD','WE','SF','SW','WF','WW')') AND a.statuskey = '1' AND 1=1 )] at
05/22/2024 10:36 PM
Hi @rushikeshvartak,
The IN condition is not working in the user update query. That is a different rule.
I have raised a different thread for this.
Regards,
Aarthi Anand
05/23/2024 12:41 AM
Hi @rushikeshvartak,
This error you mentioned in the logs shared is fixed. That is a different rule.
But the advanced query for termination condition with ##a.statustuskey isupdated## is not working.
Please assist on this. I have added the screenshots as well in the previous reply. Thank you.
Regards,
Aarthi Anand
05/23/2024 12:49 AM
The below is working fine for me. (v24.5)
a.enddate <= CURDATE() AND a.statuskey=0 AND (##a.statuskey isupdated##)
The rule is getting picked up. (Preview will not work as you statuskey updated in query)
Please check once again with the above query.
If this helps your question, please select Accept As Solution and hit Kudos
05/23/2024 01:02 AM
05/23/2024 01:17 AM - edited 05/23/2024 01:18 AM
@aarthianand93 , Perfect!
Can you please select 'Accept as Solution' for the reply (or replies) that best answered your question.
Also hit kudos!
05/24/2024 12:41 AM
Hi @aarthianand93 ,
I want to let you know the correct format to use date+1 in rules is
I also tested the below, it works fine.
a.statuskey=0 and adddate(a.enddate,1) <= curdate() and (##a.statuskey isupdated##)
Please select Accept As Solution and hit Kudos
07/24/2024 03:25 PM
@PremMahadikar @aarthianand93 Hey, when we do the date comparison at the end and start by checking the update condition, it's not working as we expected? Is my understanding correct?? Is that the fix??
07/25/2024 01:24 AM
@iam01 ,
The comparison works with update condition.
Did you exactly try the below format?
a.statuskey=0 and adddate(a.enddate,1) <= curdate() and (##a.statuskey isupdated##)