0

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;

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:

Then I tried Paritions


    SELECT A.EMPLID, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, 
    E.OPRID,LISTAGG(C.JPM_DESCR90,',') WITHIN GROUP ( ORDER BY  A.EMPLID) 
    FROM(
    SELECT *, A.EMPLID, C.JPM_DESCR90 ,row_number() OVER(PARTITION BY A.EMPLID, 
    C.JPM_DESCR90 ORDER BY A.EMPLID) as rn
    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'))
     ORDER BY A.EMPLID)
     WHERE rn=1
  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;

And got the following error:

ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause:
*Action: Error at Line: 2 Column: 15

Any suggestions would be appreciated.

5
  • You haven't told use your version of Oracle - please always include it. It's important for this question! You say you've tried solutions but none of them seem to work. Having the actual error messages would also be of great benefit - without them, we're flying blind! Look here, here and here. Jan 14 '20 at 9:05
  • LISTAGG(distinct) was implemented in newer versions of Oracle. You may need to upgrade the DB first. What version of the DB are you using? Jan 14 '20 at 11:43
  • @MichaelKutz@Vérace Its version 12.c . So the distinct version would not work Jan 14 '20 at 14:04
  • You should edit your question to add the 12 version tag
    – Joe W
    Jan 14 '20 at 14:33
  • @Joe W Thank You! Jan 14 '20 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.