This discussion is archived
5 Replies Latest reply: Nov 3, 2011 4:54 AM by BluShadow RSS

DISTINCT not working with  wmsys.wm_concat

610131 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Employee ACE
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated
    * 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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points