Thank you for supporting the Cloud Customer Connect Community in 2024. It's a gift to work with you!

Look back
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

How to remove these many duplicates of Child Role column

edited Apr 3, 2024 3:08PM in Applications Security 2 comments

Dear Experts,

The Below query producing duplicates. Could you please help out me.

Query:

==============================================================================

SELECT DISTINCT ARV.ROLE_NAME,

ARV.CODE,

--(Select ROLE_NAME from FUSION.ASE_ROLE_VL where role_id= ARRM.PARENT_ROLE_ID and ROWNUM=1)as P_Role_Name,

(SELECT TO_CHAR(listagg('Level:'|| LEVEL ||''|| Role_Name, CHR(10) ON OVERFLOW TRUNCATE)

       WITHIN GROUP (ORDER BY LEVEL))

                       

                       FROM (Select ARV1.ROLE_NAME, ARRM1.PARENT_ROLE_ID,ARRM1.CHILD_ROLE_ID FROM ASE_ROLE_ROLE_MBR ARRM1,FUSION.ASE_ROLE_VL ARV1

                       WHERE ARV1.ROLE_ID = ARRM1.PARENT_ROLE_ID

                       AND ARV1.LANGUAGE='US')ARRM1

                       WHERE 1=1

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!