DISTINCT not working with  wmsys.wm_concat

610131
    Need help on this :

    I need distinct in group concat,wmsys.wm_concat is not working in PL/SQL but its working out side PL/SQL.Am i not using function correct way ? my Oracle version 11g,R2.


    Not Working in PL/SQL :

    create or replace procedure ttp
    as
    begin
    insert into tt1(cnt1,deptno1,sumt,cbranch)
    select count(no),count(deptno),sum(tot),wmsys.wm_concat(DISTINCT branch) from tt group by deptno;
    commit;
    end;

    ERROR :
    LINE/COL ERROR
    4/1 PL/SQL: SQL Statement ignored
    5/48 PL/SQL: ORA-30482: DISTINCT option not allowed for this function
    16:42:43 SQL>


    Working in SQL :
    select count(no),count(deptno),sum(tot),wmsys.wm_concat(distinct branch) from tt group by deptno;


    Thanks,

    Edited by: user607128 on Oct 30, 2011 1:50 PM
      • 1. Re: DISTINCT not working with  wmsys.wm_concat
        damorgan
        Working demo here:
        http://www.morganslibrary.org/reference/wm_functions.html

        The link to the full library is:
        http://www.morganslibrary.org/library.html
        if you want to bookmark it.
        • 2. Re: DISTINCT not working with  wmsys.wm_concat
          Frank Kulash
          Hi,

          I don't know why DISTINCT doesn't work in PL/SQL. You might need to do two GROUP BYs; one to get distinct branches, and the other to collapse that down to one row per deptno:
          INSERT INTO tt1 (cnt1, deptno1, sumt, cbranch)
          WITH   got_distinct_branch     AS
          (
               SELECT    deptno
               ,       branch
               ,       COUNT (no)          AS count_no
               ,       COUNT (deptno)     AS count_deptno
               ,       SUM (tot)          AS sum_tot
               FROM       tt
               GROUP BY  deptno
               ,            branch
          )
          SELECT       SUM (count_no)
          ,       SUM (count_deptno)
          ,       SUM (sum_tot)
          ,       LISTAGG (branch, ',') WITHIN GROUP (ORDER BY branch)
          FROM       got_distinct_branch
          GROUP BY  deptno
          ;
          You didn't post CREATE TABLE and INSERT statements for your tables, so I can't test it.

          Don't use wm_concat; it's not documented. Since you have Oracle 11.2, you can use LISTAGG, or, if you really need the DISTINCT feature outside of PL/SQL, the user-defined STRAGG which you can copy from the following page:
          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
          • 3. Re: DISTINCT not working with  wmsys.wm_concat
            damorgan
            I wouldn't discourage someone from using WM_CONCAT if they have not yet moved to 11gR2.

            While not documented at tahiti ... it is most certainly documented by Tom Kyte:
            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:2196162600402
            and all of the other WM_ functions are documented:
            http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11826/index.htm#W

            If in 11gR2 then use LISTAGG
            http://www.morganslibrary.org/reference/analytic_functions.html#afla
            • 4. Re: DISTINCT not working with  wmsys.wm_concat
              tkyte-Oracle

              Dan,

              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402#548923200346634568

              there I wrote in regards to wm_concat:

              my suggestion is going to be consistent....

              Never use undocumented stuff, it is subject to change, removal, broken-ness without recourse.

              either

              a) use stragg
              b) write your own
              c) use the connect by trick.

              That was in 2007, in 2011 I would add:

              d) use listagg

              • 5. Re: DISTINCT not working with  wmsys.wm_concat
                BluShadow
                * faint *

                Blimey, we don't see you round here much Tom. :)

                I think I'll link to your post here in the FAQ as an "authoritative" voice on not using wm_concat.