1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Compare rows in one table with some conditions

Discussion in 'SQL PL/SQL' started by B52, Oct 7, 2009.

  1. B52

    B52 Guest

    Hi all,

    I am facing a problem and request from you a help on how to solve it.
    It is a very challenging problem.

    In either an Oracle function or PL/SQL script, I want to check the non-recovery of two rows from one table TB_CLASS that can have AT LEAST for the same set of columns (As,Bs and Cs in our case) similar values. In other words, the condition is ==>
    For two rows in the table:
    At least, one of A columns is similar AND at least one of B columns is similar AND at least one of C columns is similar.

    Then insert the primary key values of the two rows and the names of the columns in which they are similar in ONE LINE of another table TB_EXCEPTION.


    Table TB_CLASS:

    Class1|1| | |1| |1|
    Class2|1| |1|1|1|1|
    Class3| |1| |1| |1|
    Class4|1| |1| |1|1|

    For Class1 and Class2, they recover on A1,B1 and C1.
    For Class2 and Class4, they recover on A1,A3,B2 and C1
    These two lines have to be inserted in my TB_EXCEPTION table as below



    I have tought of something like that:
    Code (SQL):
    SELECT CLA1.Class,
           '<Name of columns that are equal from set As Seperated by ';'>',
           '<Name of columns that are equal from set Bs Seperated by ';'>',
           '<Name of columns that are equal from set Cs Seperated by ';'>'
           TB_CLASS CLA2,
     WHERE (   CLA1.A1 = CLA2.A1
            OR CLA1.A2 = CLA2.A2
            OR CLA1.A3 = CLA2.A3 )
       AND (   CLA1.B1 = CLA2.B1
            OR CLA1.B2 = CLA2.B2 )
       AND (   CLA1.C1 = CLA2.C1
            OR CLA1.C2 = CLA2.C2 )
    In my query, I get Class1 & Class2 but also Class2 & Class1. I want it just once.
    Also, I don't know how I can insert the name of the columns in the TB_EXCEPTION table.
    I think it needs a PL/SQL treatment and use work tables.

    Please, can you help. I would really appreciate it. I have no issue.

    Thanks a lot.

    If need more information, I can give.