Technical Rule advanced query

[userNM]

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!

[naveenss]

Hi @userNM  can you please elaborate on your requirement?

[userNM]

yes, our requirement that is these 2 values are equal user receive an account in the specific target system

[naveenss · Accepted solution]

@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. 

[userNM]

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} ?

[userNM]

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'

[naveenss]

Hi @userNM it is working for me in 24.4 

Can you please check in logs for any errors and share here?

[userNM]

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

[rushikeshvartak · Accepted solution]

Try below

a.costcenter  IN (select ev.customproperty3 from Entitlement_values ev where ev.customproperty3=a.costcenter)

[userNM]

it's working now thank you!

[rushikeshvartak]

Please provide screenshot and logs