Aggregating Certificate Names in One Row While Removing Duplicate Values

Question

In the query below, I used the LISTAGG function to aggregate certificate names I.e. JPM_DESCR90. However, now I'm trying to find a way to remove all the duplications in the row, and reducing the space between the values in the All_ CERTIFICATIONS column. For the duplications, I've used the distinct function i.e. LISTAGG( DISTINCT C.JPM_DESCR90,',') as well as a number of other functions, but none of them seem to work.

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, MAX( TO_CHAR(C.JPM_DATE_3,'YYYY-MM-DD')) AS Latest_Certification_Date ,LISTAGG( C.JPM_DESCR90,',') WITHIN GROUP ( ORDER BY C.JPM_DESCR90) AS All_Certifications 
  FROM    PS_NPS_VIEW_ALL A, (PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID ), PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.EMPL_STATUS='A'
     AND A.PAYGROUP NOT IN ('SUM','CWR'))
  GROUP BY A.EMPLID, A.JOBCODE,A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID;

The distinct function wouldn't work because I was using Oracle 12 c.

Another thing I tried was to use a nested select statement.

SELECT A.EMPLID, A.JOBCODE,A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID,
LISTAGG(C.JPM_DESCR90,','on overflow truncate with count) WITHIN GROUP ( ORDER BY C.JPM_DESCR90)  from (select unique JPM_DESCR90 JPM_DESCR90   
                  from PS_NPS_LIC_VW_RPT C  
                 where A.EMPLID = F.EMPLID)) JPM
  FROM PS_NPS_VIEW_ALL A, (PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID ), PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.EMPL_STATUS='A'
     AND A.PAYGROUP NOT IN ('SUM','CWR'))
  GROUP BY A.EMPLID, A.JOBCODE,A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID;

The above syntax gives the following error

Error at Command Line : 3 Column : 44 Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action:

I then tried regex_replace to remove duplicates.

SELECT  A.EMPLID,
RTRIM(REGEXP_REPLACE(
           (listagg(Num2,'-') WITHIN GROUP (ORDER BY C.JPM_DESCR90) OVER() ), 
           '([^-]*)(-\1)+($|-)', 
           '\1\3'), '-') C.JPM_DESCR90 ,A.JOBCODE,A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID
  FROM PS_NPS_VIEW_ALL A, (PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID ), PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.EMPL_STATUS='A'
     AND A.PAYGROUP NOT IN ('SUM','CWR'))
  GROUP BY  A.EMPLID, CERT, A.JOBCODE,A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID;

And I got the following error:

Error at Command Line : 5 Column : 27 Error report - SQL Error: ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action:

Any suggestions or alternative approaches with sample code would be appreciated.