Can't able to fetch Child Entitlement's child Entitlement(Tcode Value)

[PreethiPandi]

Hi Team,

We want to fetch Roles ->entitlement-> Child entitlement->Child entitlement(Tcode)

Below query I have used But till child entitlement it is working fine but Can't able to fetch Child Entitlement's child Entitlement(Tcode Value)

select distinct r.role_name as 'Role Name',parent.entitlement_value as parentEntitlement,child.ENTITLEMENT_VALUE as childEntitlement
from Roles r,role_entitlements re,entitlement_values parent
join entitlements2 ev2 on parent.ENTITLEMENT_VALUEKEY=ev2.ENTITLEMENT_VALUE1KEY
join entitlement_values child on child.ENTITLEMENT_VALUEKEY=ev2.ENTITLEMENT_VALUE2KEY
where re.rolekey=r.rolekey
and re.ENTITLEMENT_VALUEKEY=parent.ENTITLEMENT_VALUEKEY

Can any one please help in resolving this.

Regards,

Preethi

[armaanzahir]

Hi @PreethiPandi 

Can you check joining the entitlements3 table? That should contain the 3rd level hierarchical entitlements.

Something like this:

select distinct r.role_name as 'Role Name',parent.entitlement_value as parentEntitlement,child.ENTITLEMENT_VALUE as childEntitlement,child1.ENTITLEMENT_VALUE as grandchildEntitlement
from Roles r,role_entitlements re,entitlement_values parent
join entitlements2 ev2 on parent.ENTITLEMENT_VALUEKEY=ev2.ENTITLEMENT_VALUE1KEY
join entitlement_values child on child.ENTITLEMENT_VALUEKEY=ev2.ENTITLEMENT_VALUE2KEY
join entitlements3 ev3 on child.ENTITLEMENT_VALUEKEY=ev3.ENTITLEMENT_VALUE2KEY
join entitlement_values child1 on child1.ENTITLEMENT_VALUEKEY=ev3.ENTITLEMENT_VALUE3KEY
where re.rolekey=r.rolekey
and re.ENTITLEMENT_VALUEKEY=parent.ENTITLEMENT_VALUEKEY

[PreethiPandi]

Hi @armaanzahir ,

I have Tried it is not giving the result. getting no data found.

Regards,

Preethi

[rushikeshvartak]

Can you share screenshot of tab from which you are looking data for 

[PreethiPandi]

Hi @rushikeshvartak ,

Please find the below screen shot which I am looking for.

Regards,

Preethi

[rushikeshvartak]

SELECT r.role_name,
       ev.entitlement_value  roleent,
       ev2.entitlement_value childent
FROM   role_entitlements re
       JOIN roles r
         ON r.rolekey = re.rolekey
       JOIN entitlement_values ev
         ON ev.entitlement_valuekey = re.entitlement_valuekey
       LEFT JOIN entitlements2 e2
              ON ev.entitlement_valuekey = e2.entitlement_value1key
       LEFT JOIN entitlement_values ev2
              ON ev2.entitlement_valuekey = e2.entitlement_value2key 

[PreethiPandi]

Hi @rushikeshvartak 

We need Child Entitlement's child Entitlement(Tcode Value)

Regards,

Preethi

[rushikeshvartak]

SELECT r.role_name,
       ev.entitlement_value   roleent,
       ev2.entitlement_value  SAPROLE,
       ev22.entitlement_value TCODE
FROM   role_entitlements re
       JOIN roles r
         ON r.rolekey = re.rolekey
       JOIN entitlement_values ev
         ON ev.entitlement_valuekey = re.entitlement_valuekey
       LEFT JOIN entitlements2 e2
              ON ev.entitlement_valuekey = e2.entitlement_value1key
       LEFT JOIN entitlement_values ev2
              ON ev2.entitlement_valuekey = e2.entitlement_value2key
       LEFT JOIN entitlements2 e22
              ON e2.entitlement_value2key = e22.entitlement_value1key
       LEFT JOIN entitlement_values ev22
              ON ev22.entitlement_valuekey = e22.entitlement_value2key 

[rushikeshvartak · Accepted solution]

@PreethiPandi  Did it worked for you ?

[PreethiPandi]

Hi @rushikeshvartak ,

Thanks for your response and it has worked for me.

Regards,

preethi