Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/03/2024 02:22 AM - edited 07/03/2024 02:22 AM
Hello,
how can I write an advanced query where users.customproperty1 = entitlement_values.ENTITLEMENT_VALUE
and
users.costcenter=entitlement_values.customproperty3
I tried to write a where clause query but I think it has smth to do with case sensitivity that is why it is not working.
Thank you!
Solved! Go to Solution.
07/03/2024 02:27 AM
Hi @userNM can you please elaborate on your requirement?
07/03/2024 02:30 AM
yes, our requirement that is these 2 values are equal user receive an account in the specific target system
07/03/2024 02:34 AM
@userNM use like below
a.firstname= (select ev.entitlement_value from Entitlement_values ev where ev.entitlement_value='TestEntTechnicalRule')
Let me know if this helps. You can add anymore conditions in the similar format.
07/03/2024 05:02 AM
thank you! it worked! do you know if we can write the same condition in the SAVCUSTOMQUERY ?
so like SAVCUSTOMQUERY::ev.entitlement_value = '${users.firstname} ?
07/18/2024 05:07 AM - edited 07/18/2024 05:07 AM
hello,
the point is when we try to do it like
a.costcenter= (select ev.customproperty3 from Entitlement_values ev where ev.customproperty3=a.costcenter) it says 'Invalid Condition'
07/18/2024 06:40 AM
Hi @userNM it is working for me in 24.4
Can you please check in logs for any errors and share here?
07/18/2024 07:25 AM
2024-07-18T16:20:35+02:00-ecm-worker-services.AdImportService-quartzScheduler_Worker-3-lfgbp-DEBUG-Query to get entitlements with imported through current job: select ENTITLEMENT_VALUEKEY, customproperty2 as entColValue from ENTITLEMENT_VALUES where entitlementtypekey in (9) and JOB_ID = 32959
2024-07-18T16:18:45+02:00-ecm-util.JDBCExceptionReporter-http-nio-8080-exec-519-zkrt7-ERROR-Subquery returns more than 1 row
2024-07-18T16:18:44+02:00-ecm-hana.HanaRuleController-http-nio-8080-exec-519-zkrt7-DEBUG-Count Query is common for both basic and advanced rulesselect count(*) as CNT from Users a where ...
2024-07-18T16:18:32+02:00-ecm-services.UsersService-http-nio-8080-exec-524-zkrt7-DEBUG-Query is
2024-07-18T16:18:28+02:00-ecm-services.UsersService-http-nio-8080-exec-525-zkrt7-DEBUG-Query is
2024-07-18T16:18:26+02:00-ecm-services.UsersService-http-nio-8080-exec-528-zkrt7-DEBUG-Query is
2024-07-18T16:16:40+02:00-ecm-domain.FlatViewJobcontrolController-http-nio-8080-exec-507-zkrt7-DEBUG-ecmjobIdQuery .. select max(eij.JOBID) as 'JobId' from EcmImportJob eij where eij.TRIGGERNAME in (select qrt.TRIGGER_NAME from qrtz_triggers qrt) group by eij.TRIGGERNAME
07/18/2024 07:28 AM - edited 07/18/2024 07:28 AM
Try below
a.costcenter IN (select ev.customproperty3 from Entitlement_values ev where ev.customproperty3=a.costcenter)
07/23/2024 01:29 AM
it's working now thank you!
07/18/2024 07:27 AM
Please provide screenshot and logs