Workflow if else: Error in executing query with arithmetic operation

[Aditya]

Hi Team,

My requirement is to: fetch value from roles customproperty, accounts customproperty and add this value.

I have to check whether the sum of these two properties are greater than 100 or not.

for ex: role customproperty12 + account customproperty4 > 100

I have tried below queries that give errors as below:

Query 1: with nester query and union

(com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("select sum(score.rs) from (select r.customproperty12 as rs from roles r where r.rolekey = '${entitlement.id}' UNION select a.customproperty4 as rs from accounts a inner join user_accounts ua on a.accountkey = ua.accountkey where a.endpointkey = 140 and ua.userkey = '${user.id}') as score") > 100)

Query 1 Error:

org.jbpm.api.JbpmException: script evaluation error: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: ( near line 1, column 27 [select sum(score.rs) from (select r.customproperty12 as rs from roles r where r.rolekey = '49464' UNION select a.customproperty4 as rs from accounts a inner join user_accounts ua on a.accountkey = ua.accountkey where a.endpointkey = 140 && ua.userkey = '6389') as score]

Query 2: with + operator (without nested query and union)

(com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("select sum( r.customproperty12 + a.customproperty4) from roles r, accounts a inner join user_accounts ua on a.accountkey = ua.accountkey where r.rolekey = '${entitlement.id}' and a.endpointkey = 140 and ua.userkey = '${user.id}'") > 100)

observation: The + operator dissappears when we save the workflow. I think It won't support the + operator.

Query 2 Error:

org.jbpm.api.JbpmException: script evaluation error: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [select r.customproperty12 a.customproperty4 from roles r, accounts a inner join user_accounts ua on a.accountkey = ua.accountkey where r.rolekey = '49466' && a.endpointkey = 140 && ua.userkey = '39012']

Query 3: Simple (without nested query and union)

(com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("select r.customproperty12 from roles r where r.rolekey = '${entitlement.id}'") > 100)

Query 3 Error:

org.jbpm.api.JbpmException: script evaluation error: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: roles is not mapped [select r.customproperty12 from roles r where r.rolekey = '49466'];

None of them is working.

Workflow :

Let me know how I can achieve this use case.

Thanks,
Aditya Verma

[h_sapkota]

Hi Aditya,

Try this query:

(com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("select r.customproperty12 from Roles r where r.id = '${entitlement.id}'") > 100)

It should be in HQL.

Regards,
Hitesh

[rushikeshvartak]

R should be capital

(com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("select r.customproperty12 from Roles  r where r.rolekey = '${entitlement.id}'") > 100)