11 Replies Latest reply on Aug 15, 2016 4:48 PM by Paulzip

    I need to get duplicate rows inserted in a seperate table both are giving seperate results. Can any one explain why?

    Sreelatha Pragadapati

      insert into frfx_duplicates

      select * from

         premise_count_data_frfx a

      where

        a.rowid >

         any (

           select

              b.rowid

           from

              premise_count_data_frfx b

           where

              nvl(a.unit_number,'0') = nvl(b.unit_number,'0')     and

              nvl(a.street_number,'0') = nvl(b.street_number,'0') and

              nvl(a.directional_prefix,'0') = nvl(b.directional_prefix,'0') and

              nvl(a.street_name,'0') = nvl(b.street_name,'0') and

              nvl(a.street_name_suffix,'0') = nvl(b.street_name_suffix,'0') and

              nvl(a.street_type,'0') = nvl(b.street_type,'0') and

             nvl(a.directional_suffix,'0') = nvl(b.directional_suffix,'0') and

              nvl(a.city_name,'0') = nvl(b.city_name,'0') and

              nvl(a.province,'0') =  nvl(b.province,'0')

              );

       

      This is inserting 1551 rows

       

       

      select unit_number,street_number,directional_prefix,street_name,street_name_suffix,street_type,directional_suffix,city_name,province

      from premise_count_data_frfx

      group by unit_number,street_number,directional_prefix,street_name,street_name_suffix,street_type,directional_suffix,city_name,province

      having count(*) > 1;

       

      This gives me 1504 rows

       

      insert into frfx_duplicates select * from premise_count_data_frfx

      where (unit_number,street_number,directional_prefix,street_name,street_name_suffix,street_type,directional_suffix,city_name,province)

      in(select unit_number,street_number,directional_prefix,street_name,street_name_suffix,street_type,directional_suffix,city_name,province

      from premise_count_data_frfx

      group by unit_number,street_number,directional_prefix,street_name,street_name_suffix,street_type,directional_suffix,city_name,province

      having count(*) > 1);

       

       

      This statement says 0 rows inserted.

        • 1. Re: I need to get duplicate rows inserted in a seperate table both are giving seperate results. Can any one explain why?
          BluShadow

          How about you post some example data and what results you expect and he logic of what you are actually trying to do, rather than expect us to decipher 3 different queries on tables and data we don't know.

           

          Re: 2. How do I ask a question on the forums?

          • 2. Re: I need to get duplicate rows inserted in a seperate table both are giving seperate results. Can any one explain why?
            Paulzip

            You don't need to fabricate data in checks like...

             

              nvl(a.directional_prefix,'0') = nvl(b.directional_prefix,'0') and

              nvl(a.street_name,'0') = nvl(b.street_name,'0') and

             

            What if '0' was a valid value somewhere?

             

            Try this and report back to say if it worked...

             

            insert into frfx_duplicates

            select unit_number,street_number,directional_prefix,street_name,street_name_suffix,street_type,directional_suffix,city_name,province

            from (select f.*, row_number() over (partition by unit_number,street_number,directional_prefix,street_name,street_name_suffix,street_type,directional_suffix,city_name,province order by rowid) rn

                  from   premise_count_data_frfx f)

            where rn > 1

            1 person found this helpful
            • 3. Re: I need to get duplicate rows inserted in a seperate table both are giving seperate results. Can any one explain why?
              Sreelatha Pragadapati

              Thank you so much paul, But I could also do that..if that was of some help. The requirement is to insert all values from the table to the target. That's where I was facing problem; it gives me zero rows inserted.

              • 4. Re: I need to get duplicate rows inserted in a seperate table both are giving seperate results. Can any one explain why?
                BluShadow

                As I said, you need to post some example tables and data and show what you expect the results to be.

                 

                It sounds like you perhaps need a merge statement, but without knowing the data and the expected results, there's little point in us guessing.

                • 5. Re: I need to get duplicate rows inserted in a seperate table both are giving seperate results. Can any one explain why?
                  Jonathan Lewis

                  The 1551 vs. 1504 is not (necessarily) a problem - all you need is 47 items which appear three times: select count(*) with that query - order by count(*) to make the check easier, maybe.

                   

                  The zero rows is probably because all your duplicates have a null somewhere in their column list - the IN requires equality on all columns, and NULL doesn't satisfy equality.

                   

                  You might also like to read the following thread as it describes ways to deal with "null comparison" problems: Re: function-based indexes

                   

                  Regards

                  Jonathan Lewis

                  1 person found this helpful
                  • 6. Re: I need to get duplicate rows inserted in a seperate table both are giving seperate results. Can any one explain why?
                    Sreelatha Pragadapati

                    Thanks Jonathan...

                     

                    "The zero rows is probably because all your duplicates have a null somewhere in their column list - the IN requires equality on all columns, and NULL doesn't satisfy equality." This was true

                    • 7. Re: I need to get duplicate rows inserted in a seperate table both are giving seperate results. Can any one explain why?
                      Sreelatha Pragadapati

                      Below queries resulting in same results-1551 rows. But Why there is a difference in results using partition by and Group by (1504 rows).

                       

                      Your query:

                      select unit_number,street_number,directional_prefix,street_name,street_name_suffix,street_type,directional_suffix,city_name,province

                      from (select f.*, row_number() over (partition by unit_number,street_number,directional_prefix,street_name,street_name_suffix,street_type,directional_suffix,city_name,province order by rowid) rn

                            from   premise_count_data_frfx f)

                      where rn > 1;

                       

                      My query:

                      insert into frfx_duplicates

                      select * from

                         premise_count_data_frfx a

                      where

                        a.rowid >

                         any (

                           select

                              b.rowid

                           from

                              premise_count_data_frfx b

                           where

                              nvl(a.unit_number,'0') = nvl(b.unit_number,'0')     and

                              nvl(a.street_number,'0') = nvl(b.street_number,'0') and

                              nvl(a.directional_prefix,'0') = nvl(b.directional_prefix,'0') and

                              nvl(a.street_name,'0') = nvl(b.street_name,'0') and

                              nvl(a.street_name_suffix,'0') = nvl(b.street_name_suffix,'0') and

                              nvl(a.street_type,'0') = nvl(b.street_type,'0') and

                             nvl(a.directional_suffix,'0') = nvl(b.directional_suffix,'0') and

                              nvl(a.city_name,'0') = nvl(b.city_name,'0') and

                              nvl(a.province,'0') =  nvl(b.province,'0')

                              );

                       

                       

                       

                       

                      • 8. Re: I need to get duplicate rows inserted in a seperate table both are giving seperate results. Can any one explain why?
                        ricvar_2001

                        Hi Sreelatha Pragadapati,

                         

                        Walking back to your original post I offer to you a different approach. I hope it can help you:

                         

                        create table  premise_count_data_frfx
                        (id number,
                        unit_number number,
                        street_number number,
                        directional_prefix number,
                        street_name varchar2(30 char));
                        
                        create table  frfx_duplicates
                        (id number,
                        unit_number number,
                        street_number number,
                        directional_prefix number,
                        street_name varchar2(30 char));
                        
                        
                        insert into premise_count_data_frfx values (1,100,1.1,1.11,'a');
                        insert into premise_count_data_frfx values (2,100,1.1,1.11,'a');
                        insert into premise_count_data_frfx values (3,100,1.1,1.11,'b');
                        insert into premise_count_data_frfx values (4,100,1.1,1.11,'c');
                        insert into premise_count_data_frfx values (5,100,1.1,1.11,null);
                        insert into premise_count_data_frfx values (6,100,1.1,1.11,null);
                        insert into premise_count_data_frfx values (7,100,1.1,null,null);
                        insert into premise_count_data_frfx values (8,100,1.1,null,null);
                        commit;
                        

                         

                        select * from premise_count_data_frfx;

                         

                             

                        IDUNIT_NUMBERSTREET_NUMBERDIRECTIONAL_PREFIXSTREET_NAME
                        11001.11.11a
                        21001.11.11a
                        31001.11.11b
                        41001.11.11c
                        51001.11.11
                        61001.11.11
                        71001.1
                        81001.1

                         

                        Row 1 is a duplicate of row 2, row 6 is a duplicate of row 5 and row 8 is a duplicate of row 7.

                         

                        insert into frfx_duplicates
                        select * from premise_count_data_frfx where rowid  in (
                        select row_id from (
                            select rowid row_id, 
                                  row_number() over (partition by unit_number,street_number,directional_prefix,street_name
                                      order by id) seq
                            from premise_count_data_frfx
                        ) where seq>1);
                        

                         

                        select * from frfx_duplicates;

                             

                        IDUNIT_NUMBERSTREET_NUMBERDIRECTIONAL_PREFIXSTREET_NAME
                        21001.11.11a
                        61001.11.11
                        81001.1

                         

                        Hope this help.

                         

                        Regards,

                         

                        Ricardo

                        • 9. Re: I need to get duplicate rows inserted in a seperate table both are giving seperate results. Can any one explain why?
                          Paulzip

                          Sreelatha Pragadapati wrote:

                           

                          Below queries resulting in same results-1551 rows. But Why there is a difference in results using partition by and Group by (1504 rows).

                           

                          Your query:

                          select unit_number,street_number,directional_prefix,street_name,street_name_suffix,street_type,directional_suffix,city_name,province

                          from (select f.*, row_number() over (partition by unit_number,street_number,directional_prefix,street_name,street_name_suffix,street_type,directional_suffix,city_name,province order by rowid) rn

                          from premise_count_data_frfx f)

                          where rn > 1;

                           

                          My query:

                          insert into frfx_duplicates

                          select * from

                          premise_count_data_frfx a

                          where

                          a.rowid >

                          any (

                          select

                          b.rowid

                          from

                          premise_count_data_frfx b

                          where

                          nvl(a.unit_number,'0') = nvl(b.unit_number,'0') and

                          nvl(a.street_number,'0') = nvl(b.street_number,'0') and

                          nvl(a.directional_prefix,'0') = nvl(b.directional_prefix,'0') and

                          nvl(a.street_name,'0') = nvl(b.street_name,'0') and

                          nvl(a.street_name_suffix,'0') = nvl(b.street_name_suffix,'0') and

                          nvl(a.street_type,'0') = nvl(b.street_type,'0') and

                          nvl(a.directional_suffix,'0') = nvl(b.directional_suffix,'0') and

                          nvl(a.city_name,'0') = nvl(b.city_name,'0') and

                          nvl(a.province,'0') = nvl(b.province,'0')

                          );

                          Yes, because your query isn't correct.

                           

                          insert into frfx_duplicates

                          select * from

                            premise_count_data_frfx a

                          where

                            a.rowid >

                            any (

                              select

                                  b.rowid

                              from

                                  premise_count_data_frfx b

                              where

                                  nvl(a.unit_number,'0') = nvl(b.unit_number,'0')    and

                                  nvl(a.street_number,'0') = nvl(b.street_number,'0') and

                                  nvl(a.directional_prefix,'0') = nvl(b.directional_prefix,'0') and

                                  nvl(a.street_name,'0') = nvl(b.street_name,'0') and

                                  nvl(a.street_name_suffix,'0') = nvl(b.street_name_suffix,'0') and

                                  nvl(a.street_type,'0') = nvl(b.street_type,'0') and

                                  nvl(a.directional_suffix,'0') = nvl(b.directional_suffix,'0') and

                                  nvl(a.city_name,'0') = nvl(b.city_name,'0') and

                                  nvl(a.province,'0') =  nvl(b.province,'0')

                                  )

                           

                          is rewritten by the optimiser as...

                           

                          insert into frfx_duplicates

                          select *

                          from premise_count_data_frfx a

                          where exists (select null

                                        from premise_count_data_frfx b

                                        where nvl(a.unit_number,'0') = nvl(b.unit_number,'0')     and

                                              nvl(a.street_number,'0') = nvl(b.street_number,'0') and

                                              nvl(a.directional_prefix,'0') = nvl(b.directional_prefix,'0') and

                                              nvl(a.street_name,'0') = nvl(b.street_name,'0') and

                                              nvl(a.street_name_suffix,'0') = nvl(b.street_name_suffix,'0') and

                                              nvl(a.street_type,'0') = nvl(b.street_type,'0') and

                                              nvl(a.directional_suffix,'0') = nvl(b.directional_suffix,'0') and

                                              nvl(a.city_name,'0') = nvl(b.city_name,'0') and

                                              nvl(a.province,'0') =  nvl(b.province,'0') and

                                              a.rowid > b.rowid)

                           

                          In other words, insert any record which matches others, but has a higher rowid than any of them.

                           

                          If you have 3 cloned records with the following ROWIDs

                           

                          AAABoqAADAAAAwPAAA

                          AAABoqAADAAAAwPAAB

                          AAABoqAADAAAAwPAAC

                           

                          Both the ROWID ending in B and C fulfil that criteria.  So you are inserting 2 records rather than 1.

                           

                          I personally dislike ANY, ALL, SOME operators, especially when used with inequality operators, they are always misunderstood and invariably end up causing bugs, like in your situation.

                           

                          Another point, your query is VERY inefficient.

                          • 11. Re: I need to get duplicate rows inserted in a seperate table both are giving seperate results. Can any one explain why?
                            Paulzip

                            Sreelatha Pragadapati wrote:

                             

                            Thanks Paul!

                            Your'e welcome.

                             

                            Please remember to close your questions when answered (mark them Answered), you have a few open and it is bad forum etiquette.