5 Replies Latest reply: Nov 3, 2011 6:54 AM by BluShadow RSS

    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

                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.