Problem with Config for Requestable Entitlement in ARS

[varunpuri]

Hi,

I am trying to configure Config for Requestable Entitlement in ARS in the Entitlement Type and getting an exception in the logs.

I am trying to do this configuration : ev.customproperty1='${showHideEntitlement}'

And the dynamic attribute named showHideEntitlement is of type Single Select from SQL query.

The query is : select distinct case when u.customproperty40 is not null and lower(u.customproperty40) = 'savadmin' then 'ADMIN' else 'NOTADMIN' end from users u where u.userkey in (${requestee})

And I get the following error in logs :

nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: savadmin near line 1, column 824 [ select new Map(ev.entitlement_value as entitlement_value,ev.id as id,ev.entitlementtypekey as entitlementtypekey ,ev.description as description,ev.entitlement_glossary as entitlement_glossary,ev.syscritical as syscritical,ev.soxcritical as soxcritical,ev.risk as risk,ev.displayname as displayname ) from com.saviynt.ecm.identitywarehouse.domain.Entitlement_values ev where ev.status=1 and ev.entitlementtypekey.id =:entitlement_types and ev.id not in (:entitlementnotinset) and ev.id not in ( Select emap.entitlement_value2key.id from com.saviynt.ecm.identitywarehouse.domain.EntitlementMap emap where emap.entitlement_value1key.id in (:entitlementnotinset) and emap.excludeEntitlement = true) and (ev.customproperty1='select distinct case when u.customproperty40 is not null and lower(u.customproperty40) = 'savadmin' then 'ADMIN' else 'NOTADMIN' end from users u where u.userkey in (${requestee})') order by ifnull(ev.displayname,ev.entitlement_value) asc]

Request your assistance here.

Best Regards,
Varun

[SumathiSomala]

@varunpuri please try with below sample

select distinct CASE WHEN (u.customproperty40 IS NOT NULL AND lower(u.customproperty40 = 'savadmin')) then 'ADMIN' else 'NOTADMIN' end as ID from users u where u.userkey='${requestee}'

select distinct CASE WHEN (u.customproperty40 IS NOT NULL AND lower(u.customproperty40 = 'savadmin')) then 'ADMIN' else 'NOTADMIN' end as ID from users u where u.userkey in (${requestee})

Also you can validate the data with below query

select distinct CASE WHEN (u.customproperty40 IS NOT NULL AND lower(u.customproperty40 = 'savadmin')) then 'ADMIN' else 'NOTADMIN' end as ID from users u where userkey=481

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

[varunpuri]

Hello @SumathiSomala ,

Thank you for the response. Same problem persists with all the above approaches. It throws exception as soon as it encounters 'savadmin' literal within the query.

Best Regards,
Varun

[SumathiSomala]

Did you validate the provided query in data analyzer? 

Where and when it is throwing the error?