7 Replies Latest reply on Aug 30, 2019 1:17 PM by mathguy

    Compare values in same table

    AnishRV

      We have table with below data

      Table A

      Item Id     Item_Number1     Item_Number2

           1          1001                    2001

           2          1002                    1003

           3          1003                    2003

       

      We have to built a query to find is any 'Item_number1' column values are exists in 'Item_number2' columns.

        • 1. Re: Compare values in same table
          mNem

          with t (itemid, col1, col2) as

          (

          select 1, 1001,  2001 from dual union all

          select 2, 1002,  1003 from dual union all

          select 3, 1003,  2003 from dual union all

          select 4, 2003,  1002 from dual

          )

          select a.*, b.* from t a join t b

          on (a.col1 = b.col2)

          1 person found this helpful
          • 2. Re: Compare values in same table
            L. Fernigrini

            Try with:

             

            SELECT * FROM TableA s

            WHERE EXISTS (SELECT 1 FROM TableA x WHERE x.Item_Number2 = s.Item_Number1);

             

             

            • 3. Re: Compare values in same table
              Frank Kulash

              Hi,

               

              Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.
              Explain, using specific examples, how you get those results from that data.
              Always say what version of Oracle you're using (e.g. 12.2.0.2.0).
              See the forum FAQ: Re: 2. How do I ask a question on the forums?

              • 4. Re: Compare values in same table
                Solomon Yakobson

                Just for fun, hierarchical solution:

                 

                with t (itemid, col1, col2) as

                (

                select 1, 1001,  2001 from dual union all

                select 2, 1002,  1003 from dual union all

                select 3, 1003,  2003 from dual union all

                select 4, 2003,  1002 from dual

                )

                select  distinct col2 col1

                  from  t

                  where level = 2

                  connect by col2 = prior col1

                         and level = 2

                /

                 

                      COL1

                ----------

                      2003

                      1002

                      1003

                 

                SQL>

                 

                SY.

                • 5. Re: Compare values in same table
                  mathguy

                  One more way to skin the cat... for fun, as Solomon's proposed solution, but not only. This may actually perform decently well (as can Solomon's solution); both solutions only require one pass over the base table. Join (and semi-join) solutions will likely require two passes.

                   

                  So - here goes. I wrote it in a way that works in Oracle 11.1 and higher**, where we can use UNPIVOT. The solution can be adapted for older versions, if needed.

                   

                  ** Except in the WITH clause, which is not part of the solution anyway (you should use your actual table and column names).

                   

                  with

                    a (item_id, item_number1, item_number2) as (

                      select 1, 1001, 2001 from dual union all

                      select 2, 1002, 1003 from dual union all

                      select 3, 1003, 2003 from dual union all

                      select 4, 2003, 1002 from dual

                    )

                  select  item_number

                  from    a

                  unpivot (item_number for col in (item_number1 as 1, item_number2 as 2))

                  group   by item_number

                  having  min(col) != max(col)

                  order   by item_number                 --  if needed

                  ;

                   

                   

                  ITEM_NUMBER

                  -----------

                         1002

                         1003

                         2003

                  • 6. Re: Compare values in same table
                    Solomon Yakobson

                    mathguy wrote:

                     

                    both solutions only require one pass over the base table.

                    Don't be tricked by unpivot - it always requires multiple passes. This can be easily checked:

                     

                    SQL> create table a(item_id, item_number1, item_number2) as

                      2  select 1, 1001, 2001 from dual union all

                      3  select 2, 1002, 1003 from dual union all

                      4  select 3, 1003, 2003 from dual union all

                      5  select 4, 2003, 1002 from dual

                      6  /

                     

                    Table created.

                     

                    SQL> variable c clob

                    SQL> begin

                      2      dbms_utility.expand_sql_text('

                      3  select  item_number

                      4  from    a

                      5  unpivot (item_number for col in (item_number1 as 1, item_number2 as 2))

                      6  group  by item_number

                      7  having  min(col) != max(col)',:c);

                      8  end;

                      9  /

                     

                    PL/SQL procedure successfully completed.

                     

                    SQL> set long 1000000

                    SQL> print c

                     

                    C

                    --------------------------------------------------------------------------------

                    SELECT  "A1"."ITEM_NUMBER" "ITEM_NUMBER"

                      FROM  (

                            SELECT  "A2"."ITEM_ID" "ITEM_ID",

                                    "A2"."COL" "COL",

                                    "A2"."ITEM_NUMBER" "ITEM_NUMBER"

                              FROM  (

                                      SELECT  "A4"."ITEM_ID" "ITEM_ID",

                                              1 "COL",

                                              "A4"."ITEM_NUMBER1" "ITEM_NUMBER"

                                         FROM "SCOTT"."A" "A4"

                                     UNION ALL

                                      SELECT "A4"."ITEM_ID" "ITEM_ID",

                                             2 "COL","A4"."ITEM_NUMBER2" "ITEM_NUMBER"

                                        FROM "SCOTT"."A" "A4"

                                    ) "A2"

                              WHERE "A2"."ITEM_NUMBER" IS NOT NULL

                            ) "A1"

                      GROUP BY "A1"."ITEM_NUMBER"

                      HAVING MIN("A1"."COL") <> MAX("A1"."COL")

                     

                    SY.

                    • 7. Re: Compare values in same table
                      mathguy

                      Solomon Yakobson wrote:

                       

                      Don't be tricked by unpivot - it always requires multiple passes. This can be easily checked:

                       

                      You did say that before. As far as I recall, a long discussion ensued, and - also as far as I recall - the conclusion was that in fact unpivot does NOT require more than one pass (although what you show is true: EXPAND_SQL_TEXT says unpivot performs union all).

                       

                      For what it's worth, since unpivot can be implemented efficiently, and at very little "software development cost", simply with a cross join - the way it was done in the old days - I would find it very odd if the Oracle programmers chose to implement it inefficiently when they added pivot/unpivot in version 11.1.

                       

                      I will look for the old discussion and link to it if I can find it.

                       

                      EDIT:   Here it is. You can look through the whole thread if you like (it was about a specific question where UNPIVOT could be used, but some of the discussion was, separately, addressing exactly the claim you repeated in this thread). The link below goes straight to one of the Replies relevant to the claim about UNPIVOT.  Re: How to take advantage of data already being "grouped" in rows