#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    5
    Rep Power
    0

    Exclamation Listagg(to_clob()) getting duplicates


    Hi All,
    I am getting duplicate data repeated in the same column when I use LISTAGG.

    the query that I use:

    select
    pi.unix_account_x||'=>'||pi.producer_name_x "Producer_Name]",
    listagg(to_clob(pi.unix_account_x)||'=>'||nvl(pp.pace_group_x,'N.A.'),',') within group (order by pp.pace_group_x) "Supp_Cntct[SM_Group]",
    listagg(to_clob(nvl(pp.email_list_x,'N.A.')),',') within group (order by pp.pace_group_x) "Supp_Cntct[eMail]"
    from pi,pp

    can someone help?
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388
    Originally Posted by epir
    Hi All,
    I am getting duplicate data repeated in the same column when I use LISTAGG.

    . . . E t c . . .

    can someone help?
    1) LISTAGG does not eliminate duplicates.
    2) You are missing the GROUP BY clause
    3) TO_CLOB() does not remove the "ORA-01489: result of string concatenation is too long" error.


    I guessed on the grouping (partitioning), but you could try something like this:
    Code:
    SELECT producer_name "Producer_Name]"
         , LISTAGG ( supp_cntct_sm_group, ',') WITHIN GROUP (ORDER BY pp.pace_group_x) "Supp_Cntct[SM_Group]"
         , LISTAGG ( supp_cntct_email, ',') WITHIN GROUP (ORDER BY pp.pace_group_x) "Supp_Cntct[eMail]"
      FROM (SELECT pi.unix_account_x || '=>' || pi.producer_name_x producer_name
                 , pi.unix_account_x || '=>' || NVL (pp.pace_group_x, 'N.A.') supp_cntct_sm_group
                 , NVL (pp.email_list_x, 'N.A.') supp_cntct_email
                 , pp.pace_group_x
                 , ROW_NUMBER () OVER ( PARTITION BY pi.unix_account_x || '=>' || NVL (pp.pace_group_x, 'N.A.') 
                                        ORDER BY pp.pace_group_x) rn
              FROM pi, pp)
     WHERE rn = 1
     GROUP BY producer_name
     ORDER BY producer_name;

IMN logo majestic logo threadwatch logo seochat tools logo