Query for Config for Requestable Entitlement in ARS

[rama_roy]

Hi Team,

We are having two dynamic variables AccessType and UserAccessType where AccessType value is entitlement customproperty5(production,Non-Production,Allow List etc)  and UserAccessType is entitlement customproperty6. We need to hide UserAccessType in ARS page that is working fine.

Entitlement Type is below:

and Config for Requestable Entitlement in ARS is configured as ev.customproperty5 ='${AccessType}' and (ev.customproperty6 ='${UserAccessType}' or ev.customproperty6='All') and its working.

We are getting the below view of ARS page with these configurations where we have to select Access Type from the drop down and based on that selection user will get the name of the Groups.

Now, the requirement is to select 'All' the cp5 value and get the entitlements for all of them. basically, if user select 'All' for AccessType then he will be seeing all the entitlement having cp5 updated and cp6 will be either "User" or "All'. We have tried the below to achieve this but that is not working.

Dynamic Attribute:

Select distinct ev.customproperty5 as ID from entitlement_values ev,entitlement_types et, endpoints ep where ev.ENTITLEMENTTYPEKEY= et.ENTITLEMENTTYPEKEY and et.ENDPOINTKEY =ep.ENDPOINTKEY and ep.endpointname = 'Application name'
union
Select 'All' as ID from entitlement_values ev

The above query is working in data analyzer.

Config for Requestable Entitlement in ARS:

CASE WHEN '${AccessType}'='All' THEN {(ev.customproperty5 is not null) and (ev.customproperty6 ='${UserAccessType}'or ev.customproperty6='All')}
else
'${AccessType}'=ev.customproperty5 and (ev.customproperty6 ='${UserAccessType}' or ev.customproperty6='All')
END

Could you please help me to correct the case statement?

Regards,

Rama

[Raghu]

@rama_roy  try below

CASE WHEN '${AccessType}'='All' THEN ((ev.customproperty5 is not null) and (ev.customproperty6 ='${UserAccessType}'or ev.customproperty6='All'))
else
'${AccessType}'=ev.customproperty5 and (ev.customproperty6 ='${UserAccessType}' or ev.customproperty6='All')
END

[rama_roy]

Hi Raghu, 

I am getting same error.

Regards,

Rama

Regards,

Rama

[rama_roy · Accepted solution]

(ev.customproperty5 ='${AccessType}' OR ('${AccessType}' = 'All' AND ev.customproperty5 IS NOT NULL)) and (ev.customproperty6 ='${UserAccessType}' or ev.customproperty6='All')  This query is working and giving correct result.

Regards,

Rama

[rushikeshvartak · Accepted solution]

✅👍Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.