3 Replies Latest reply: Jul 30, 2013 11:31 AM by 1009115 RSS

    Identify Different Groups in the Organization (Group the Same data together)

    1009115

      Version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

       

      Help me in Identifying the groups(different Sectors) in the Organization

      Each Group will have the same Group id if  We_org_id , We_addr_id or We_org_id , Ein Matches

      In the below Example Row 1 and 2 Are linked with We_org_id , We_addr_id

      and row 2 and 3 are linked with We_org_id , Ein  so all the three rows has the same groupid

       

      Included Output Required.

       

      Example

       

      311    563          72-1500000    2

      311    563          72-1500001    2

      311    565          72-1500001    2

       

      -- Table and inserts

      CREATE TABLE ORG_MISMATCH
      (
        WE_ORG_ID   NUMBER,
        WE_ADDR_ID  NUMBER,
        EIN         VARCHAR2(30 BYTE)
      );
      
      SET DEFINE OFF;
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 24303142, '31-1700059');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '39-1675361');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '31-1700059');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '26-0060245');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '72-1284709');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 121786868, '31-1700059');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 21053495, '72-1355929');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '73-1317052');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '56-2525845');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '72-1355929');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '84-1535762');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '91-2031795');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '84-1487943');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '91-2035844');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '84-1535753');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '72-1501788');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 56381251, '30-0137738');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 563, '72-1500001');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 565, '72-1500001');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '72-1355929');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 24303142, '31-1700059');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '31-1700059');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 121786868, '31-1700059');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '84-1535762');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '72-1501788');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '91-2031795');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '30-0137738');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '72-1284709');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '56-2525845');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '91-2035844');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 21053495, '72-1355929');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '39-1675361');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '84-1487943');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '84-1535753');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '26-0060245');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (999, 56381251, '73-1317052');
      Insert into ORG_MISMATCH
         (WE_ORG_ID, WE_ADDR_ID, EIN)
      Values
         (311, 563, '72-1500000');
      COMMIT;
      
      
      
      
      
      WE_ORG_IDWE_ADDR_IDEINGROUPID

      OUTPUT REQUIRED    --> Create Groupid Column

      3112430314231-17000591
      3115638125139-16753611
      3115638125131-17000591
      3115638125126-00602451
      3115638125172-12847091
      31112178686831-17000591
      3112105349572-13559291
      3115638125173-13170521
      3115638125156-25258451
      3115638125172-13559291
      3115638125184-15357621
      3115638125191-20317951
      3115638125184-14879431
      3115638125191-20358441
      3115638125184-15357531
      3115638125172-15017881
      3115638125130-01377381
      311563   72-15000012
      311565   72-15000012
      311563   72-15000002
        • 1. Re: Identify Different Groups in the Organization (Group the Same data together)
          APC

          Please identify the rule which means that

          311 21053495    72-1355929

          is GroupId  #1 whereas

          311    563     72-1500001

            is GroupId  #2 Cheers, APC

          • 2. Re: Identify Different Groups in the Organization (Group the Same data together)
            chris227

            It would be nice to know the roots. I did assume two with

            start with we_addr_id in (

            select min(we_addr_id)

            from org_mismatch group by we_org_id

            Otherwise it could taken a while ...

             

            That below doesnt really work ..

             

            select
            WE_ORG_ID
            ,WE_ADDR_ID
            ,EIN
            ,dense_rank() over (partition by WE_ORG_ID order by r) grp
            from (
            select
            WE_ORG_ID
            ,WE_ADDR_ID
            ,EIN
            ,connect_by_root WE_ADDR_ID r
            from ORG_MISMATCH  
            connect by nocycle
            prior we_org_id = we_org_id
            and (
            (prior we_addr_id = we_addr_id and prior ein < ein)
            or
            (prior ein = ein and prior we_addr_id < we_addr_id)
            )
            start with we_addr_id in (
            select min(we_addr_id)
            from org_mismatch group by we_org_id
            )
            )
            group by
            WE_ORG_ID
            ,WE_ADDR_ID
            ,EIN
            ,r
            order by
            WE_ORG_ID
            ,WE_ADDR_ID
            ,EIN
            ,R

             

            WE_ORG_IDWE_ADDR_IDEINGRP
            31156372-15000001
            31156372-15000011
            31156572-15000011
            3112105349572-13559292
            3115638125172-13559292
            3115638125172-15017882
            3115638125173-13170522
            3115638125184-14879432
            3115638125184-15357532
            3115638125184-15357622
            3115638125191-20317952
            3115638125191-20358442
            9992105349572-13559291
            9995638125172-13559291
            9995638125172-15017881
            9995638125173-13170521
            9995638125184-14879431
            9995638125184-15357531
            9995638125184-15357621
            9995638125191-20317951
            9995638125191-20358441

             

            Message was edited by: chris227 comment

            • 3. Re: Identify Different Groups in the Organization (Group the Same data together)
              1009115

              Thanks Chris, This work for the Requirement. I will run couple of test cases.