14 Replies Latest reply: Apr 3, 2013 8:54 AM by Gurjeet RSS

    query gaves duplicate rows due to wm_concat()

    Gurjeet
      Here is the query that i need to run

      SELECT distinct UI.INDEX_NAME,ui.owner ,UI.TABLE_NAME ,wm_concat(uic.column_name) over (partition by uic.index_name order by column_position ) column_name ,ui.index_type, parameters
                               FROM
                               all_INDEXES UI JOIN all_IND_COLUMNS UIC
                               ON UIC.INDEX_NAME = UI.INDEX_NAME
                               where
                               SUBSTR(ui.index_name,1,3) not IN ('PK_', 'UQ_','DR$')
                               and ui.index_name not like 'SYS_C00%'
                               and ui.index_name not like 'SYS_IOT_TOP_%' and owner='USER1'
                          order by UI.INDEX_NAME



      The problem is wm_concat() because the table all_IND_COLUMNS contains the duplicate entry for index (index1 of type table(column1,column2,column3))

      for this wm_concat return the result as below:
      --------------------
      column1
      column1,column2
      column1,column2,column3

      But in *result i need only this for one particular index-name (distinct index)

      as a example i have two index in schema::::
      index1 on table1(column1,column2,column3) and
      index2 on table2(column5)

      need this result
      row1-- index1 column1,column2,column3
      row2-- index2 column5


      my query returns
      row1-- index1 column1
      row2-- index1 column1,column2
      row3-- index1 column1,column2,column3
      row4-- index2 column5
        • 1. Re: query gaves duplicate rows due to wm_concat()
          jeneesh
          Dont use WM_CONCAT.. It is undocumented.. Use LISTAGG if you are on the latest version..
          • 2. Re: query gaves duplicate rows due to wm_concat()
            Gurjeet
            i tried this but popup with error


            SELECT distinct UI.INDEX_NAME,ui.owner ,UI.TABLE_NAME ,
                      listagg(column_name,',') within group ( order by column_position) column_name ,ui.index_type, parameters
                                     FROM
                                     all_INDEXES UI JOIN all_IND_COLUMNS UIC
                                     ON UIC.INDEX_NAME = UI.INDEX_NAME
                                     where
                                     SUBSTR(ui.index_name,1,3) not IN ('PK_', 'UQ_','DR$')
                                     and ui.index_name not like 'SYS_C00%'
                                     and ui.index_name not like 'SYS_IOT_TOP_%'
                                     group by uic.index_name
                                     order by UI.INDEX_NAME
            • 3. Re: query gaves duplicate rows due to wm_concat()
              jeneesh
              What error you got?

              Few points:

              No need to use DISTINCT with GROUP BY
              You are not having all the required columns in the GROUP BY
              • 4. Re: query gaves duplicate rows due to wm_concat()
                Gurjeet
                ok . let me check
                • 5. Re: query gaves duplicate rows due to wm_concat()
                  Purvesh K
                  Another guess would be that you are not on 11g R2 and hence not able to use LISTAGG.

                  Here are few alternatives, Oracle String Aggregation. Mine favourite is Sys_Connect_By_Path and Row_Number.

                  Unless you tell us the error you are encountering, we would be left to play the guessing games.
                  • 6. Re: query gaves duplicate rows due to wm_concat()
                    367852
                    You haven't mentioned the error or oracle version. listagg is valid from 11g Rel 2

                    Soln1: Use wm_concat and use "group by UI.INDEX_NAME, ui.owner, UI.TABLE_NAME, ui.index_type, parameters"



                    Soln2: (select wm_concat(column_name) from all_IND_COLUMNS uic
                    where UIC.INDEX_NAME = UI.INDEX_NAME
                    group by UIC.INDEX_NAME) as column_list
                    For this solution change your sql, Avoid joining with all_IND_COLUMNS directly in the query, in this case distinct is also not required. [NOT TESTED]
                    e.g
                    SELECT UI.INDEX_NAME,
                    ui.owner ,
                    UI.TABLE_NAME ,
                    (select wm_concat(column_name) from all_IND_COLUMNS uic
                    where UIC.INDEX_NAME = UI.INDEX_NAME
                    group by UIC.INDEX_NAME),
                    ui.index_type,
                    parameters
                    FROM
                    all_INDEXES UI
                    where .................
                    • 7. Re: query gaves duplicate rows due to wm_concat()
                      BluShadow
                      smart000 wrote:
                      You haven't mentioned the error or oracle version. listagg is valid from 11g Rel 2

                      Soln1: Use wm_concat and use "group by UI.INDEX_NAME, ui.owner, UI.TABLE_NAME, ui.index_type, parameters"



                      Soln2: (select wm_concat(column_name) from all_IND_COLUMNS uic
                      where UIC.INDEX_NAME = UI.INDEX_NAME
                      group by UIC.INDEX_NAME) as column_list
                      For this solution change your sql, Avoid joining with all_IND_COLUMNS directly in the query, in this case distinct is also not required. [NOT TESTED]
                      e.g
                      SELECT UI.INDEX_NAME,
                      ui.owner ,
                      UI.TABLE_NAME ,
                      (select wm_concat(column_name) from all_IND_COLUMNS uic
                      where UIC.INDEX_NAME = UI.INDEX_NAME
                      group by UIC.INDEX_NAME),
                      ui.index_type,
                      parameters
                      FROM
                      all_INDEXES UI
                      where .................
                      Using WM_CONCAT is not a solution... It is an undocumented function and Oracle does not support it, or any application developed using it. It's functionality may also change in future release without warning, or it may be removed from the database without warning. It is stupid to use undocumented functions.

                      Unless of course, you know better than Oracle or Tom Kyte? Re: DISTINCT not working with  wmsys.wm_concat
                      • 8. Re: query gaves duplicate rows due to wm_concat()
                        Gurjeet
                        need this result
                        row1-- index1 column1,column2,column3
                        row2-- index2 column5

                        my query returns
                        =====c1=====c2==
                        row1-- index1 column1
                        row2-- index1 column1,column2
                        row3-- index1 column1,column2,column3
                        row4-- index2 column5
                        • 9. Re: query gaves duplicate rows due to wm_concat()
                          367852
                          Oracle Version ?
                          • 11. Re: query gaves duplicate rows due to wm_concat()
                            Gurjeet
                            hi jeneesh


                            SELECT UI.INDEX_NAME,ui.owner ,UI.TABLE_NAME ,
                            listagg(column_name,',') within group ( order by column_position) column_name ,ui.index_type, parameters
                            FROM
                            all_INDEXES UI JOIN all_IND_COLUMNS UIC
                            ON UIC.INDEX_NAME = UI.INDEX_NAME
                            where
                            SUBSTR(ui.index_name,1,3) not IN ('PK_', 'UQ_','DR$')
                            and ui.index_name not like 'SYS_C00%'
                            and ui.index_name not like 'SYS_IOT_TOP_%' and owner='USER1'
                            group by uic.index_name,ui.owner ,UI.TABLE_NAME ,ui.index_type, parameters
                            order by UI.INDEX_NAME

                            ORA-00979: not a GROUP BY expression
                            • 12. Re: query gaves duplicate rows due to wm_concat()
                              jeneesh
                              Are you trying this?
                              SELECT UI.INDEX_NAME,
                                ui.owner ,
                                UI.TABLE_NAME ,
                                ui.index_type,
                                parameters,
                                listagg(column_name,',') within GROUP (
                              ORDER BY column_position) column_name
                              FROM all_INDEXES UI
                              JOIN all_IND_COLUMNS UIC
                              ON  ( UIC.INDEX_NAME                    = UI.INDEX_NAME
                                    and uic.index_owner = ui.owner)
                              WHERE SUBSTR(ui.index_name,1,3) NOT IN ('PK_', 'UQ_','DR$')
                              AND ui.index_name NOT LIKE 'SYS_C00%'
                              AND ui.index_name NOT LIKE 'SYS_IOT_TOP_%'
                              GROUP BY UI.INDEX_NAME,
                                ui.owner ,
                                UI.TABLE_NAME ,
                                ui.index_type,
                                parameters
                              ORDER BY UI.INDEX_NAME;
                              • 13. Re: query gaves duplicate rows due to wm_concat()
                                367852
                                Your Select has UI.INDEX_NAME, Group by has uic.index_name. Use same column at both places.
                                • 14. Re: query gaves duplicate rows due to wm_concat()
                                  Gurjeet
                                  thanks jeneesh ,.. it works


                                  error because of "group by uic.index_name," (i used alias uic)

                                  Edited by: Mr. singh on Apr 3, 2013 7:23 PM