How to remove these many duplicates of Child Role column
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