1 2 Previous Next 25 Replies Latest reply: Jul 24, 2014 7:00 PM by Etbin RSS

    Generate exception on column comparison

    2716340

      I need some help on the generating a SQL query or even a table population so that I can query from that table after the comparison is made...Sorry..I'm not able paste the table view to show it visually....

      Below shown is the 1st table data with the Dept_No as the Primary key

      SELECT 

      777 AS DEPT_NO,

      100 AS  LIST1_EMP_ID1,

      'Raj' AS LIST1_EMP_NAME1,

      101 AS  LIST1_EMP_ID2,

      'Lee' AS LIST1_EMP_NAME2,

      NULL AS  LIST1_EMP_ID3,

      NULL AS LIST1_EMP_NAME3,

      103 AS  LIST1_EMP_ID4,

      'Sid' AS LIST1_EMP_NAME4,

      105 AS  LIST1_EMP_ID5,

      'Ann' AS LIST1_EMP_NAME5

      FROM dual

      UNION ALL

      SELECT

      888 AS DEPT_NO,

      200 AS  LIST1_EMP_ID1,

      'Betty' AS LIST1_EMP_NAME1,

      202 AS  LIST1_EMP_ID2,

      'James' AS LIST1_EMP_NAME2,

      201 AS  LIST1_EMP_ID3,

      'Jacob' AS LIST1_EMP_NAME3,

      NULL AS  LIST1_EMP_ID4,

      'John' AS LIST1_EMP_NAME4,

      204 AS  LIST1_EMP_ID5,

      NULL AS LIST1_EMP_NAME5

      FROM dual

       

      Below is the 2nd table data with the Dept_No as the Primary key

       

      SELECT 

      777 AS DEPT_NO,

      103 AS  LIST1_EMP_ID1,

      'Sid' AS LIST1_EMP_NAME1,

      105 AS  LIST1_EMP_ID2,

      'Ann' AS LIST1_EMP_NAME2,

      100 AS  LIST1_EMP_ID3,

      'Saj' AS LIST1_EMP_NAME3,

      NULL AS  LIST1_EMP_ID4,

      'Lee' AS LIST1_EMP_NAME4,

      103 AS  LIST1_EMP_ID5,

      'SAM' AS LIST1_EMP_NAME5

      FROM dual

      UNION ALL

      SELECT

      888 AS DEPT_NO,

      400 AS  LIST1_EMP_ID1,

      'Ral' AS LIST1_EMP_NAME1,

      200 AS  LIST1_EMP_ID2,

      'Betty' AS LIST1_EMP_NAME2,

      500 AS  LIST1_EMP_ID3,

      'Jacob' AS LIST1_EMP_NAME3,

      NULL AS  LIST1_EMP_ID4,

      NULL AS LIST1_EMP_NAME4,

      NULL AS  LIST1_EMP_ID5,

      NULL AS LIST1_EMP_NAME5

      FROM dual

       

      Comparison Criteria

      1) The join should be based on the Dept_No and If the EMP_ID matches then the Names need to be compared.

      2) If the EMP_ID matches the EMP_ID exception would be Matching' but if the name don't match then the Name exception would be "Not Matching"

      3) We can keep the Table1 (List1) static and place the matching records from List2 accordingly and if there are additional ones in List2 that need to be marked as "Not Matching"

      4) If there are duplicates in the EMP_ID then other than the matching one it should be marked as "Not Matching"

       

      Please find the results for the above Listings:

      SELECT 

      777 AS DEPT_NO,

      100 AS  LIST1_EMP_ID1,

      100 AS LIST2_EMP_ID1,

      'Matching' AS EMP_ID1_EXCEPTION,

      'Raj' AS LIST1_EMP_NAME1,

      'Saj' AS LIST2_EMP_NAME1,

      'Not Matching' AS EMP_NAME1_EXCEPTION,

      101 AS  LIST1_EMP_ID2,

      NULL AS LIST2_EMP_ID2,

      'Not Matching' AS EMP_ID2_EXCEPTION,

      'Lee' AS LIST1_EMP_NAME2,

      'Lee' AS LIST2_EMP_NAME2,

      'Matching' AS EMP_NAME2_EXCEPTION,

       

      NULL AS  LIST1_EMP_ID3,

      103 AS LIST2_EMP_ID3,

      'Not Matching' AS EMP_ID3_EXCEPTION,

      NULL AS LIST1_EMP_NAME3,

      'SAM' AS LIST2_EMP_NAME3,

      'Not Matching' AS EMP_NAME3_EXCEPTION,

       

      103 AS  LIST1_EMP_ID4,

      103 AS LIST2_EMP_ID4,

      'Matching' AS EMP_ID3_EXCEPTION,

      'Sid' AS LIST1_EMP_NAME4,

      'Sid' AS LIST2_EMP_NAME4,

      'Matching' AS EMP_NAME4_EXCEPTION,

       

      105 AS  LIST1_EMP_ID5,

      105 AS LIST2_EMP_ID5,

      'Matching' AS EMP_ID3_EXCEPTION,

      'Ann' AS LIST1_EMP_NAME5,

      'Ann' AS LIST2_EMP_NAME5,

      'Matching' AS EMP_NAME5_EXCEPTION

      FROM DUAL

      UNION ALL

       

       

      SELECT 

      888 AS DEPT_NO,

      200 AS  LIST1_EMP_ID1,

      200 AS LIST2_EMP_ID1,

      'Matching' AS EMP_ID1_EXCEPTION,

      'Betty' AS LIST1_EMP_NAME1,

      'Betty' AS LIST2_EMP_NAME1,

      'Matching' AS EMP_NAME1_EXCEPTION,

      202 AS  LIST1_EMP_ID2,

      NULL AS LIST2_EMP_ID2,

      'Not Matching' AS EMP_ID2_EXCEPTION,

      'James' AS LIST1_EMP_NAME2,

      NULL AS LIST2_EMP_NAME2,

      'Not Matching' AS EMP_NAME2_EXCEPTION,

      201 AS  LIST1_EMP_ID3,

      NULL AS LIST2_EMP_ID3,

      'Not Matching' AS EMP_ID3_EXCEPTION,

      'Jacob' AS LIST1_EMP_NAME3,

      NULL AS LIST2_EMP_NAME3,

      'Not Matching' AS EMP_NAME3_EXCEPTION,

       

      NULL AS  LIST1_EMP_ID4,

      500 AS LIST2_EMP_ID4,

      'Not Matching' AS EMP_ID3_EXCEPTION,

      'John' AS LIST1_EMP_NAME4,

      'Jacob' AS LIST2_EMP_NAME4,

      'Not Matching' AS EMP_NAME4_EXCEPTION,

       

      204 AS  LIST1_EMP_ID5,

      400 AS LIST2_EMP_ID5,

      'Not Matching' AS EMP_ID3_EXCEPTION,

      NULL AS LIST1_EMP_NAME5,

      'Ral' AS LIST2_EMP_NAME5,

      'Not Matching' AS EMP_NAME5_EXCEPTION

      FROM DUAL

        • 1. Re: Generate exception on column comparison
          Frank Kulash

          Hi,

           

          Whenever you have a question, please post CREATE TABLE and INSERT statements for all the tables involved, so the people who want to help you can re-create the problem and test their ideas.  Also post the results you want from that data, and an explanation of how you get those results from that data.

          In the case of a DML operation (such as UPDATE) the sample data should show what the tables are like before the DML, and the results will be the contents of the changed table(s) after the DML.

          Explain, using specific examples, how you get those results from that data.

          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

          See the forum FAQ: https://forums.oracle.com/message/9362002

          • 2. Re: Generate exception on column comparison
            2716340

            Sorry...Oracle version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

             

            Table1 Creattion & Insert statement :

             

            CREATE TABLE EMP_LIST1

            (

              DEPT_NO         NUMBER,                     

            LIST1_EMP_ID1    NUMBER,                     

            LIST1_EMP_NAME1  VARCHAR2(100),                

            LIST1_EMP_ID2    NUMBER,                    

            LIST1_EMP_NAME2  VARCHAR2(100),                

            LIST1_EMP_ID3    NUMBER,                     

            LIST1_EMP_NAME3  VARCHAR2(100),                

            LIST1_EMP_ID4    NUMBER,                     

            LIST1_EMP_NAME4  VARCHAR2(100),                

            LIST1_EMP_ID5    NUMBER,                     

            LIST1_EMP_NAME5  VARCHAR2(100)

            )

             

            INSERT INTO EMP_LIST1

            SELECT

            777 AS DEPT_NO,

            103 AS  LIST1_EMP_ID1,

            'Sid' AS LIST1_EMP_NAME1,

            105 AS  LIST1_EMP_ID2,

            'Ann' AS LIST1_EMP_NAME2,

            100 AS  LIST1_EMP_ID3,

            'Saj' AS LIST1_EMP_NAME3,

            NULL AS  LIST1_EMP_ID4,

            'Lee' AS LIST1_EMP_NAME4,

            103 AS  LIST1_EMP_ID5,

            'SAM' AS LIST1_EMP_NAME5

            FROM dual

            UNION ALL

            SELECT

            888 AS DEPT_NO,

            400 AS  LIST1_EMP_ID1,

            'Ral' AS LIST1_EMP_NAME1,

            200 AS  LIST1_EMP_ID2,

            'Betty' AS LIST1_EMP_NAME2,

            500 AS  LIST1_EMP_ID3,

            'Jacob' AS LIST1_EMP_NAME3,

            NULL AS  LIST1_EMP_ID4,

            NULL AS LIST1_EMP_NAME4,

            NULL AS  LIST1_EMP_ID5,

            NULL AS LIST1_EMP_NAME5

            FROM dual

             

            Table2 Creation & Insert statement :

            CREATE TABLE EMP_LIST2

            (

              DEPT_NO         NUMBER,                     

            LIST2_EMP_ID1    NUMBER,                     

            LIST2_EMP_NAME1  VARCHAR2(100),                

            LIST2_EMP_ID2    NUMBER,                    

            LIST2_EMP_NAME2  VARCHAR2(100),                

            LIST2_EMP_ID3    NUMBER,                     

            LIST2_EMP_NAME3  VARCHAR2(100),                

            LIST2_EMP_ID4    NUMBER,                     

            LIST2_EMP_NAME4  VARCHAR2(100),                

            LIST2_EMP_ID5    NUMBER,                     

            LIST2_EMP_NAME5  VARCHAR2(100)

            )

            INSERT INTO EMP_LIST2

            SELECT

            777 AS DEPT_NO,

            103 AS  LIST2_EMP_ID1,

            'Sid' AS LIST2_EMP_NAME1,

            105 AS  LIST2_EMP_ID2,

            'Ann' AS LIST2_EMP_NAME2,

            100 AS  LIST2_EMP_ID3,

            'Saj' AS LIST2_EMP_NAME3,

            NULL AS  LIST2_EMP_ID4,

            'Lee' AS LIST2_EMP_NAME4,

            103 AS  LIST2_EMP_ID5,

            'SAM' AS LIST2_EMP_NAME5

            FROM dual

            UNION ALL

            SELECT

            888 AS DEPT_NO,

            400 AS  LIST2_EMP_ID1,

            'Ral' AS LIST2_EMP_NAME1,

            200 AS  LIST2_EMP_ID2,

            'Betty' AS LIST2_EMP_NAME2,

            500 AS  LIST2_EMP_ID3,

            'Jacob' AS LIST2_EMP_NAME3,

            NULL AS  LIST2_EMP_ID4,

            NULL AS LIST2_EMP_NAME4,

            NULL AS  LIST2_EMP_ID5,

            NULL AS LIST2_EMP_NAME5

            FROM dual

             

            Comparison Criteria

            1) The join should be based on the Dept_No and If the EMP_ID matches then the Names need to be compared.

            2) If the EMP_ID matches the EMP_ID exception would be Matching' but if the name don't match then the Name exception would be "Not Matching"

            3) We can keep the Table1 (List1) static and place the matching records from List2 accordingly and if there are additional ones in List2 that need to be marked as "Not Matching"

            4) If there are duplicates in the EMP_ID then other than the matching one it should be marked as "Not Matching"

             

            The result from the above 2 table query should look as shown below:

            SELECT 

            777 AS DEPT_NO,

            100 AS  LIST1_EMP_ID1,

            100 AS LIST2_EMP_ID1,

            'Matching' AS EMP_ID1_EXCEPTION,

            'Raj' AS LIST1_EMP_NAME1,

            'Saj' AS LIST2_EMP_NAME1,

            'Not Matching' AS EMP_NAME1_EXCEPTION,

            101 AS  LIST1_EMP_ID2,

            NULL AS LIST2_EMP_ID2,

            'Not Matching' AS EMP_ID2_EXCEPTION,

            'Lee' AS LIST1_EMP_NAME2,

            'Lee' AS LIST2_EMP_NAME2,

            'Matching' AS EMP_NAME2_EXCEPTION,

            NULL AS  LIST1_EMP_ID3,

            103 AS LIST2_EMP_ID3,

            'Not Matching' AS EMP_ID3_EXCEPTION,

            NULL AS LIST1_EMP_NAME3,

            'SAM' AS LIST2_EMP_NAME3,

            'Not Matching' AS EMP_NAME3_EXCEPTION,

            103 AS  LIST1_EMP_ID4,

            103 AS LIST2_EMP_ID4,

            'Matching' AS EMP_ID3_EXCEPTION,

            'Sid' AS LIST1_EMP_NAME4,

            'Sid' AS LIST2_EMP_NAME4,

            'Matching' AS EMP_NAME4_EXCEPTION,

            105 AS  LIST1_EMP_ID5,

            105 AS LIST2_EMP_ID5,

            'Matching' AS EMP_ID3_EXCEPTION,

            'Ann' AS LIST1_EMP_NAME5,

            'Ann' AS LIST2_EMP_NAME5,

            'Matching' AS EMP_NAME5_EXCEPTION

            FROM DUAL

            UNION ALL

            SELECT 

            888 AS DEPT_NO,

            200 AS  LIST1_EMP_ID1,

            200 AS LIST2_EMP_ID1,

            'Matching' AS EMP_ID1_EXCEPTION,

            'Betty' AS LIST1_EMP_NAME1,

            'Betty' AS LIST2_EMP_NAME1,

            'Matching' AS EMP_NAME1_EXCEPTION,

            202 AS  LIST1_EMP_ID2,

            NULL AS LIST2_EMP_ID2,

            'Not Matching' AS EMP_ID2_EXCEPTION,

            'James' AS LIST1_EMP_NAME2,

            NULL AS LIST2_EMP_NAME2,

            'Not Matching' AS EMP_NAME2_EXCEPTION,

            201 AS  LIST1_EMP_ID3,

            NULL AS LIST2_EMP_ID3,

            'Not Matching' AS EMP_ID3_EXCEPTION,

            'Jacob' AS LIST1_EMP_NAME3,

            NULL AS LIST2_EMP_NAME3,

            'Not Matching' AS EMP_NAME3_EXCEPTION,

            NULL AS  LIST1_EMP_ID4,

            500 AS LIST2_EMP_ID4,

            'Not Matching' AS EMP_ID3_EXCEPTION,

            'John' AS LIST1_EMP_NAME4,

            'Jacob' AS LIST2_EMP_NAME4,

            'Not Matching' AS EMP_NAME4_EXCEPTION,

            204 AS  LIST1_EMP_ID5,

            400 AS LIST2_EMP_ID5,

            'Not Matching' AS EMP_ID3_EXCEPTION,

            NULL AS LIST1_EMP_NAME5,

            'Ral' AS LIST2_EMP_NAME5,

            'Not Matching' AS EMP_NAME5_EXCEPTION

            FROM DUAL

            • 3. Re: Generate exception on column comparison
              2716340

              I have added the result.

              • 4. Re: Generate exception on column comparison
                Frank Kulash

                Hi,

                 

                Sorry, I still don't understand how you get those results from that sample data.  Post a step-by-step explanation of how you might get those results (which may or may not be how the query will work).  For example: "First, look at emp_list1.list1_emp_id1.  Then look at emp_list2.list2_demp_id1.  If they have the same value ... If they don't have the same value, then ... In the case of dept_no=777, they do match, so ... but for dept_no=888 they do not match, so ...  Next, look at ..."

                • 5. Re: Generate exception on column comparison
                  2716340

                  Thanks a lot for your support Frank....

                   

                  Sure.Please find the step by step :

                  1) EMP_NAMES and EMP_IDs from LIST1 need to be compared to EMP_NAMES and EMP_IDs from LIST2 based on the Dept_No from both the tables

                  2) Each EMP_ID from LIST2 needs to be taken and compared and if you find a match then the corresponding "Names needs to be compared (Eg: In the output EMP_ID-100 matches in both LIST1 table and LIST2 table but the names doesn't match so the result should show LIST1_EMP_ID value,LIST2_EMP_ID value,EMP_ID1_EXCEPTION as 'Matching' but for the name the exception is "Not Matching")

                  3) Now if the LIST2 value do not match or not available then we needs to mark it as "Not Matching" in the exception column similarly if the "LIST1 value is not available in LIST2 then need to mark it as "Not Matching" in the exception column.

                   

                  Note :

                  • Basically its comparing the values from list1 and list2 and marking as "Matching" and "Not Matching". The additional step we are adding here is to also capture the additional EMP_IDs and EMP_NAMES in both List1 and List2 which do not match.
                  • Initially I was planning to insert all the List1 values into a temp table ( LIST2 columns and Exception Columns would be NULL
                  • After the insertion, take each EMP_ID from List2 and compare against inserted LIST1 value "If it matches then update the corresponding LIST2 Column otherwise Update the 5th column with the exception field as "Not Matching".So basically if its not available then start storing from the last column of LIST2 in the TEMP table and if its matching then store on the corresponding LIST2 field.This waywe capture all the values from LIST1 and LIST2

                   

                  Please let me know if this helped.

                  • 6. Re: Generate exception on column comparison
                    Frank Kulash

                    Hi,

                    2716340 wrote:

                     

                    Thanks a lot for your support Frank....

                     

                    Sure.Please find the step by step :

                    1) EMP_NAMES and EMP_IDs from LIST1 need to be compared to EMP_NAMES and EMP_IDs from LIST2 based on the Dept_No from both the tables

                    2) Each EMP_ID from LIST2 needs to be taken and compared and if you find a match then the corresponding "Names needs to be compared (Eg: In the output EMP_ID-100 matches in both LIST1 table and LIST2 table but the names doesn't match so the result should show LIST1_EMP_ID value,LIST2_EMP_ID value,EMP_ID1_EXCEPTION as 'Matching' but for the name the exception is "Not Matching")

                    3) Now if the LIST2 value do not match or not available then we needs to mark it as "Not Matching" in the exception column similarly if the "LIST1 value is not available in LIST2 then need to mark it as "Not Matching" in the exception column.

                     

                    Note :

                    • Basically its comparing the values from list1 and list2 and marking as "Matching" and "Not Matching". The additional step we are adding here is to also capture the additional EMP_IDs and EMP_NAMES in both List1 and List2 which do not match.
                    • Initially I was planning to insert all the List1 values into a temp table ( LIST2 columns and Exception Columns would be NULL
                    • After the insertion, take each EMP_ID from List2 and compare against inserted LIST1 value "If it matches then update the corresponding LIST2 Column otherwise Update the 5th column with the exception field as "Not Matching".So basically if its not available then start storing from the last column of LIST2 in the TEMP table and if its matching then store on the corresponding LIST2 field.This waywe capture all the values from LIST1 and LIST2

                     

                    Please let me know if this helped.

                    That helps some.

                     

                    Why do you have 5 pairs of (id, name) columns on each row?  Why don't you have 1 (id, name) pair on each row, and up to 5 times as many rows?

                     

                    In the output for dept_id=777, why is 100 in columns list1_emp_id1 and list2_emp_id1?  Why not in columns list1_emp_id2 and list2_emp_id2, or in columns list1_emp_id5 and list2_emp_id5?  Does it matter?

                     

                    Say one table has ids 101, 102, 103, 104 and 105 for a given dept_no, and the other table has ids 299, 298, 297, 296 and 295.  Which 5 do you want to include in the output, and why?

                    • 7. Re: Generate exception on column comparison
                      2716340

                      In the above example None of them matches but we need all of them to be displayed as result showing as "Not Matching"..The order really doesn't matter

                      • 8. Re: Generate exception on column comparison
                        Frank Kulash

                        Hi,

                        2716340 wrote:

                         

                        In the above example None of them matches but we need all of them to be displayed as result showing as "Not Matching"..The order really doesn't matter

                        Sorry, I don't understand.  In the sample results you posted, list1_idx and list2_idx (1 <= x <= 5) were never unequal; that is, either both columns were equal, or one of them was NULL   If all 10 mis-matched ids are to be displayed, then you must want unequal values in list1_idx and list2_idx.  When do you want to leave one of the columns NULL, and when do you want to have unequal values in them?

                        • 9. Re: Generate exception on column comparison
                          2716340

                          In my result I matched the List1 and List2 with the EMP_IDs first and for the matching EMP_IDs tried to match the Names.Now for the Non Matched ones it really doesn't matter where we place them as they don't match each other.There is no specific requirement to leave them as NULLs if it doesn't match..The requirement is place the matching one's next to each other and then the rest can be in any columns with the value "Not Matching"

                          • 10. Re: Re: Generate exception on column comparison
                            Peter vd Zwan

                            Hi,

                             

                            Try this and see if this comes close to what you want.

                            To see how it works see the outcome after you comment out the pivot clause after match_1.

                             

                             

                            with list_A as

                            (SELECT

                              777   AS DEPT_NO

                            , 100   AS LIST1_EMP_ID1

                            , 'Raj' AS LIST1_EMP_NAME1

                            , 101   AS LIST1_EMP_ID2

                            , 'Lee' AS LIST1_EMP_NAME2

                            , NULL  AS LIST1_EMP_ID3

                            , NULL  AS LIST1_EMP_NAME3

                            , 103   AS LIST1_EMP_ID4

                            , 'Sid' AS LIST1_EMP_NAME4

                            , 105   AS LIST1_EMP_ID5

                            , 'Ann' AS LIST1_EMP_NAME5

                            FROM

                              dual

                            UNION ALL

                            SELECT

                              888     AS DEPT_NO

                            , 200     AS LIST1_EMP_ID1

                            , 'Betty' AS LIST1_EMP_NAME1

                            , 202     AS LIST1_EMP_ID2

                            , 'James' AS LIST1_EMP_NAME2

                            , 201     AS LIST1_EMP_ID3

                            , 'Jacob' AS LIST1_EMP_NAME3

                            , NULL    AS LIST1_EMP_ID4

                            , 'John'  AS LIST1_EMP_NAME4

                            , 204     AS LIST1_EMP_ID5

                            , NULL    AS LIST1_EMP_NAME5

                            FROM

                              dual

                            )

                            ,list_B as

                            (

                            SELECT

                              777   AS DEPT_NO

                            , 103   AS LIST1_EMP_ID1

                            , 'Sid' AS LIST1_EMP_NAME1

                            , 105   AS LIST1_EMP_ID2

                            , 'Ann' AS LIST1_EMP_NAME2

                            , 100   AS LIST1_EMP_ID3

                            , 'Saj' AS LIST1_EMP_NAME3

                            , NULL  AS LIST1_EMP_ID4

                            , 'Lee' AS LIST1_EMP_NAME4

                            , 103   AS LIST1_EMP_ID5

                            , 'SAM' AS LIST1_EMP_NAME5

                            FROM

                              dual

                            UNION ALL

                            SELECT

                              888     AS DEPT_NO

                            , 400     AS LIST1_EMP_ID1

                            , 'Ral'   AS LIST1_EMP_NAME1

                            , 200     AS LIST1_EMP_ID2

                            , 'Betty' AS LIST1_EMP_NAME2

                            , 500     AS LIST1_EMP_ID3

                            , 'Jacob' AS LIST1_EMP_NAME3

                            , NULL    AS LIST1_EMP_ID4

                            , NULL    AS LIST1_EMP_NAME4

                            , NULL    AS LIST1_EMP_ID5

                            , NULL    AS LIST1_EMP_NAME5

                            FROM

                              dual

                            )

                            ,

                            match_1 as

                            (select

                              a.dept_no

                            --  ,a.id_no  id_no_a

                            --  ,b.id_no  id_no_b

                              ,a.id     id_a

                              ,b.id     id_b

                              ,decode(a.id, b.id, 'Matching', 'not matching') id_exception

                              ,a.name   name_a

                              ,b.name   name_b

                              ,decode(a.name, b.name, 'Matching', 'not matching') name_exception

                              ,count(*) over (partition by coalesce(a.dept_no, b.dept_no) order by coalesce(a.dept_no, b.dept_no), a.id_no, b.id_no) rn

                            from

                              list_A          unpivot ( (id,name) for (id_no,name_no )in ( (list1_emp_id1, list1_emp_name1)   as (1,1)

                                                                                            ,(list1_emp_id2, list1_emp_name2) as (2,2)

                                                                                            ,(list1_emp_id3, list1_emp_name3) as (3,3)

                                                                                            ,(list1_emp_id4, list1_emp_name4) as (4,4)

                                                                                            ,(list1_emp_id5, list1_emp_name5) as (5,5)

                                                                                         )

                                                      )    a

                            full outer join list_B     unpivot ( (id,name) for (id_no,name_no )in ( (list1_emp_id1, list1_emp_name1)   as (1,1)

                                                                                            ,(list1_emp_id2, list1_emp_name2) as (2,2)

                                                                                            ,(list1_emp_id3, list1_emp_name3) as (3,3)

                                                                                            ,(list1_emp_id4, list1_emp_name4) as (4,4)

                                                                                            ,(list1_emp_id5, list1_emp_name5) as (5,5)

                                                                                         )

                                                      )    b on ( b.dept_no     = a.dept_no

                                                                  and b.id   = a.id)

                            )

                            select

                              *

                            from

                              match_1  pivot ( max(id_a)  id_a

                                              ,max(id_b)  id_b

                                              ,max(id_exception) id_exception

                                              ,max(name_a) name_a

                                              ,max(name_b) name_b

                                              ,max(name_exception) name_exception

                                              for rn in (1,2,3,4,5) )

                            where

                              dept_no is not null

                            ;

                             

                            Regards,

                             

                            Peter

                            • 11. Re: Generate exception on column comparison
                              2716340

                              This is excellent stuff...Very close Peter...

                               

                              See in the below scenario Ricky is getting missed out...The Non-matching ones from List_B got missed out

                               

                               

                              with list_A as

                               

                              (SELECT

                               

                              777 AS DEPT_NO,

                              161 AS  LIST1_EMP_ID1,

                              'Christopher' AS LIST1_EMP_NAME1,

                              213 AS  LIST1_EMP_ID2,

                              'Gerding' AS LIST1_EMP_NAME2,

                              215 AS  LIST1_EMP_ID3,

                              'Joseph' AS LIST1_EMP_NAME3,

                              484 AS  LIST1_EMP_ID4,

                              'Janel' AS LIST1_EMP_NAME4,

                              991 AS  LIST1_EMP_ID5,

                              'Kevin' AS LIST1_EMP_NAME5

                              FROM  dual

                              )

                               

                              ,list_B as

                               

                              (

                               

                              SELECT

                              777 AS DEPT_NO,

                              991 AS  LIST1_EMP_ID1,

                              'Kevin' AS LIST1_EMP_NAME1,

                              216 AS  LIST1_EMP_ID2,

                              'Ricky' AS LIST1_EMP_NAME2,

                              216 AS  LIST1_EMP_ID3,

                              'Ricky' AS LIST1_EMP_NAME3,

                              216 AS  LIST1_EMP_ID4,

                              'Ricky' AS LIST1_EMP_NAME4,

                              216 AS  LIST1_EMP_ID5,

                              'Ricky B ' AS LIST1_EMP_NAME5

                              FROM  dual

                              )

                              , match_1 as

                               

                                  (select

                               

                                    a.dept_no

                                    ,a.id     id_a

                                    ,b.id     id_b

                                    ,decode(a.id, b.id, 'Matching', 'not matching') id_exception

                                    ,a.name   name_a

                                    ,b.name   name_b

                                    ,decode(a.name, b.name, 'Matching', 'not matching') name_exception

                                    ,count(*) over (partition by coalesce(a.dept_no, b.dept_no) order by coalesce(a.dept_no, b.dept_no), a.id_no, b.id_no) rn

                               

                                  from

                               

                                    list_A          unpivot ( (id,name) for (id_no,name_no )in ( (list1_emp_id1, list1_emp_name1)   as (1,1)

                               

                                                                                                  ,(list1_emp_id2, list1_emp_name2) as (2,2)

                               

                                                                                                  ,(list1_emp_id3, list1_emp_name3) as (3,3)

                               

                                                                                                  ,(list1_emp_id4, list1_emp_name4) as (4,4)

                               

                                                                                                  ,(list1_emp_id5, list1_emp_name5) as (5,5)

                               

                                                                                               )

                               

                                                            )    a

                               

                                  full outer join list_B     unpivot ( (id,name) for (id_no,name_no )in ( (list1_emp_id1, list1_emp_name1)   as (1,1)

                               

                                                                                                  ,(list1_emp_id2, list1_emp_name2) as (2,2)

                               

                                                                                                  ,(list1_emp_id3, list1_emp_name3) as (3,3)

                               

                                                                                                  ,(list1_emp_id4, list1_emp_name4) as (4,4)

                               

                                                                                                  ,(list1_emp_id5, list1_emp_name5) as (5,5)

                               

                                                                                               )

                               

                                                            )    b on ( b.dept_no     = a.dept_no

                               

                                                                        and b.id   = a.id)

                               

                                  )

                               

                                  select  *

                                 from

                                 match_1  pivot ( max(id_a)  id_a

                               

                                                    ,max(id_b)  id_b

                               

                                                    ,max(id_exception) id_exception

                               

                                                    ,max(name_a) name_a

                               

                                                    ,max(name_b) name_b

                               

                                                    ,max(name_exception) name_exception

                               

                                                    for rn in (1,2,3,4,5) )

                               

                                  where

                               

                                    dept_no is not null

                               

                                  ;

                              • 12. Re: Re: Generate exception on column comparison
                                Peter vd Zwan

                                Hi,

                                 

                                You need more columns if you want to be able to have 5 ID's from A shown not matched to B and 5 ID's from B not matched to A so I included 6 to 10 in the pivot clause.

                                 

                                WITH

                                  list_A AS

                                  (SELECT

                                    777           AS DEPT_NO

                                  , 161           AS LIST1_EMP_ID1

                                  , 'Christopher' AS LIST1_EMP_NAME1

                                  , 213           AS LIST1_EMP_ID2

                                  , 'Gerding'     AS LIST1_EMP_NAME2

                                  , 215           AS LIST1_EMP_ID3

                                  , 'Joseph'      AS LIST1_EMP_NAME3

                                  , 484           AS LIST1_EMP_ID4

                                  , 'Janel'       AS LIST1_EMP_NAME4

                                  , 991           AS LIST1_EMP_ID5

                                  , 'Kevin'       AS LIST1_EMP_NAME5

                                  FROM

                                    dual

                                  )

                                ,list_B AS

                                  (SELECT

                                    777        AS DEPT_NO

                                  , 991        AS LIST1_EMP_ID1

                                  , 'Kevin'    AS LIST1_EMP_NAME1

                                  , 216        AS LIST1_EMP_ID2

                                  , 'Ricky'    AS LIST1_EMP_NAME2

                                  , 216        AS LIST1_EMP_ID3

                                  , 'Ricky'    AS LIST1_EMP_NAME3

                                  , 216        AS LIST1_EMP_ID4

                                  , 'Ricky'    AS LIST1_EMP_NAME4

                                  , 216        AS LIST1_EMP_ID5

                                  , 'Ricky B ' AS LIST1_EMP_NAME5

                                  FROM

                                    dual

                                  )

                                , match_1 AS

                                  (SELECT

                                    COALESCE(a.dept_no, b.dept_no)          DEPT_NO      --a.dept_no

                                  ,a.id id_a

                                  ,b.id id_b

                                  ,DECODE(a.id, b.id, 'Matching', 'not matching') id_exception

                                  ,a.name name_a

                                  ,b.name name_b

                                  ,DECODE(a.name, b.name, 'Matching', 'not matching') name_exception

                                  ,COUNT(*) over (partition BY COALESCE(a.dept_no, b.dept_no) order by COALESCE(a.dept_no, b.dept_no), a.id_no, b.id_no) rn

                                  FROM

                                    list_A unpivot ( (id,name) FOR (id_no,name_no ) IN ( (list1_emp_id1, list1_emp_name1) AS (1,1)

                                                                                        ,(list1_emp_id2, list1_emp_name2) AS (2,2)

                                                                                        ,(list1_emp_id3, list1_emp_name3) AS (3,3)

                                                                                        ,(list1_emp_id4, list1_emp_name4) AS (4,4)

                                                                                        ,(list1_emp_id5, list1_emp_name5) AS (5,5)

                                                                                        )

                                                  ) a

                                  FULL OUTER JOIN

                                    list_B unpivot ( (id,name) FOR (id_no,name_no ) IN ( (list1_emp_id1, list1_emp_name1) AS (1,1)

                                                                                        ,(list1_emp_id2, list1_emp_name2) AS (2,2)

                                                                                        ,(list1_emp_id3, list1_emp_name3) AS (3,3)

                                                                                        ,(list1_emp_id4, list1_emp_name4) AS (4,4)

                                                                                        ,(list1_emp_id5, list1_emp_name5) AS (5,5)

                                                                                       )

                                                   ) b ON ( b.dept_no = a.dept_no AND b.id = a.id)

                                )

                                SELECT

                                  *

                                FROM

                                  match_1 pivot (  MAX(id_a) id_a

                                                  ,MAX(id_b) id_b

                                                  ,MAX(id_exception) id_exception

                                                  ,MAX(name_a) name_a

                                                  ,MAX(name_b) name_b

                                                  ,MAX(name_exception) name_exception FOR rn IN (1,2,3,4,5,6,7,8,9,10) ) --) )

                                --WHERE

                                --  dept_no IS NOT NULL

                                ;

                                 

                                 

                                Hope this works for you.

                                 

                                Regards,

                                 

                                Peter

                                • 13. Re: Generate exception on column comparison
                                  2716340

                                  As you mentioned if we have 5 names from A shown not matched to B and 5 names from B not matched to A then we can place them in whatever columns and display them as "Not Matching". The requirement is only have 5 columns.So in this case except for "Kevin" with ID 991 all other can be

                                  161 'Christopher' not matching with 216 'Ricky'. The order or the IDs doesn't really matter after the matching is completed.

                                  • 14. Re: Re: Generate exception on column comparison
                                    Peter vd Zwan

                                    Hi,

                                     

                                    So what was wrong then in you sample?

                                    It did show 4 ID's from A not matching to B, and one (991, Kevin) matching.

                                     

                                    Why do you say you miss Ricky? He is in the 6 to 9 columns which you don't want.

                                     

                                     

                                    Regards,

                                     

                                    Peter

                                    1 2 Previous Next