1 Reply Latest reply: May 28, 2014 2:09 AM by Gary Graham-Oracle RSS

    To be able to view duplicate rows.

    1052595
      rowid in (select rowid from (
      SELECT
        rowid,
        regexp_replace(a.LM_REQUEST_NUM,'[[:space:]][[:cntrl:]]', '') as aaa,
        regexp_replace(a.email_id,'[[:space:]][[:cntrl:]]', '')as bbb,
        COUNT(*) over (partition by regexp_replace(a.LM_REQUEST_NUM,'[[:space:]][[:cntrl:]]', ''),
        regexp_replace(a.email_id,'[[:space:]][[:cntrl:]]', '')) as cnt
      FROM anchor_23_may_2014 a 
      ) where cnt>1)
      

       

      In the filter text box  of the data tab of a table when I type the above I want to be able to see the row and its duplicate.

      There could be duplicates in the table if  the first two columns are compared.

      CREATE TABLE "SCOTT"."TEST_TAB" 
         ( "EMPNO" NUMBER(4,0), 
        "ENAME" VARCHAR2(10 BYTE), 
        "JOB" VARCHAR2(9 BYTE), 
        "MGR" NUMBER(4,0), 
        "HIREDATE" DATE, 
        "SAL" NUMBER(7,2), 
        "COMM" NUMBER(7,2), 
        "DEPTNO" NUMBER(2,0)
         )
      

       

       

      Insert into TEST_TAB (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (111,'JONES','CLEANER',null,null,null,null,null);
      Insert into TEST_TAB (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (111,'JONES','CEO',null,null,null,null,null);
      Insert into TEST_TAB (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (222,'DAWES','ASSISTANT',null,null,null,null,null);
      

       

      Here row 2 is considered a duplicate of row 1 according to our business requirement.
      I need to be able to see all such rows which have same content in the first two columns in SQL Developer data tab of table.
      Based on the content of the third column I will be deciding if I need to delete row/s.(Which again I want to be able to do by clicking red X button on data tab in SQL Developer)

      The query that I first pasted in this thread is what I tried (pasting in Filter text box of data tab of SQL Developer) and hit  "ORA-00942: table or view does not exist"

      I feel I am doing something wrong here. Please help.

       

       

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      "CORE 11.2.0.3.0 Production"
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      

       

      Thanks.

        • 1. Re: To be able to view duplicate rows.
          Gary Graham-Oracle

          You did not provide a complete test case (no anchor_23_may_2014 info!), but if you place that expression in the filter, then the modified SQL looks like this ...

          select  /*+ NO_PARALLEL */ * from (

              SELECT

                 ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN",

                 EMPNO EMPNO, ENAME ENAME, JOB JOB, MGR MGR, HIREDATE HIREDATE, SAL SAL, COMM COMM, DEPTNO DEPTNO

              FROM "SCOTT"."TEST_TAB"

          )  WHERE rowid in (select rowid from

                    SELECT 

                       rowid, 

                       regexp_replace(a.LM_REQUEST_NUM,'[[:space:]][[:cntrl:]]', '') as aaa, 

                       regexp_replace(a.email_id,'[[:space:]][[:cntrl:]]', '')as bbb, 

                       COUNT(*) over (partition by regexp_replace(a.LM_REQUEST_NUM,'[[:space:]][[:cntrl:]]', ''), 

                       regexp_replace(a.email_id,'[[:space:]][[:cntrl:]]', '')) as cnt 

                     FROM anchor_23_may_2014 a  

                  ) where cnt>1)

          Try debugging this query in a worksheet using Run Script (F5) on your SQL Developer.  If you have an Oracle DB client with SQL*Plus installed, you also may wish to cross-check it there.


          Regards,

          Gary

          SQL Developer Team