Forum Stats

  • 3,854,962 Users
  • 2,264,441 Discussions
  • 7,905,847 Comments

Discussions

Comparing two tables with different structure

akshayvpn
akshayvpn Member Posts: 2
edited May 13, 2014 5:27PM in SQL & PL/SQL

Hello,

I have two tables with different structure. Basically I wanted to compare values in these two tables where one table returns a single row for 'QUANTITY BREAK'. and other table returns multiple rows for QUANTITY BREAK.

Table A:

PART_NO

QTY_BRK1

QTY_BRK2

QTY_BRK3

QTY_BRK4

PROMO_BRK1

PROMO_BRK2

PROMO_BRK3

PROMO_BRK4

LIST_PRICE_BRK1

LIST_PRICE_BRK2

LIST_PRICE_BRK3

LIST_PRICE_BRK4

Sample Record:

PART_NOQTY_BRK1QTY_BRK2QTY_BRK3QTY_BRK4PROMO_BRK1PROMO_BRK2PROMO_BRK3PROMO_BRK4LIST_PRICE_BRK1LIST_PRICE_BRK2LIST_PRICE_BRK3LIST_PRICE_BRK4
100900099000999000999999990001601601601602000200020002000

Table B:

PART_NO

QUANTITY

PRICE

Sample Record:

PART_NOQUANTITYPRICE
10010.016
100100.016
100250.024
1001000.016
10010000.016

I want to find all the PART_NO's where there is a discrepancy in the Table 1 QTY BREAKS and Table 2 QUANTITY values.
In table A, QTY_BRK values are considered as follows:
QTY_BRK1 = 9000 i.e. 1-9

QTY_BRK2 = 99000 i.e. 10-99

QTY_BRK3 = 999000 i.e. 100-999

QTY_BRK4 = 99999999000 i.e. 1000-100000000

So you can see the discrepancy in above example. Table A gives 4 quantity breaks, but table B gives 5 quantity breaks for the same PART_NO. (an extra qty break of 10-25 is present)

This way I need help in writing a query which will give me all the PART_NOs which are having discrepancy in both tables.

Thanks.

Tagged:

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    This range like 1-9, 10-99 .. and 10-25 where are they defined. How to identify which quantity fall in which range. Because you have two range 10-99 and 10-25 they are overlapping. So that is kind of confusing.

  • L-MachineGun
    L-MachineGun Member Posts: 926 Silver Badge
    edited May 14, 2014 10:52AM

    Like this?:

    SQL> WITH Tablea (Part_No, Qty_Brk1, Qty_Brk2, Qty_Brk3, Qty_Brk4)

      2    AS (SELECT 100, 9000, 99000, 999000, 99999999000 FROM DUAL)

      3     , Tableb (Part_No, Quantity, Price)

      4    AS (SELECT 100,    1, 0.016 FROM DUAL UNION

      5        SELECT 100,   10, 0.016 FROM DUAL UNION

      6        SELECT 100,   25, 0.024 FROM DUAL UNION

      7        SELECT 100,  100, 0.016 FROM DUAL UNION

      8        SELECT 100, 1000, 0.016 FROM DUAL)

      9  SELECT B.*, (B.Quantity * 10 - 1) * 1000 Brk

    10    FROM Tablea A, Tableb B

    11   WHERE B.Part_No = A.Part_No

    12          AND NOT ( ((B.Quantity * 10 - 1) * 1000 = A.Qty_Brk1)

    13               OR   ((B.Quantity * 10 - 1) * 1000 = A.Qty_Brk2)

    14               OR   ((B.Quantity * 10 - 1) * 1000 = A.Qty_Brk3)

    15               OR   ((B.Quantity * 10 - 1) * 1000 = A.Qty_Brk4));

       PART_NO   QUANTITY      PRICE BRK_NOTFND

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

           100         25       .024     249000

           100       1000       .016    9999000

    This query will return the "Qty breaks" from "TableB" not found in "TableA".

  • akshayvpn
    akshayvpn Member Posts: 2

    Hi Karthick,

    That is the discrepancy which we are supposed to find out. The two ranges for the PART_NO 100 (10-99 in TableA and 10-25 in TableB), out of these the 10-25 is a incorrect one which I don't want. 10-99 is the correct. As I have mentioned in the problem statement, TableB is having a extra QTY_BRK (QUANTITY in TableB is nothing but the QTY_BRK from TableA).

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited May 13, 2014 5:28PM

    Hi,

    You can pivot table_b so that it has 1 row per part_no, like table_b.  Then it will be easier to compare them.

    Do some parts have fewer price breaks, and therefore have NULLs in the qty_brk columns of table_a (and/or fewer rows in table_b)?  If so, DECODE can make comparing them easier.

    I think you want something like this:

    WITH   b_numbered    AS
    (
        SELECT  part_no
        ,       (quantity - 1) * 1000    AS qty_brk
        ,       ROW_NUMBER () OVER ( PARTITION BY  part_no
                                     ORDER BY      quantity
                                   )  AS r_num
        FROM    table_b
    )
    ,    b_pivoted    AS
    (
        SELECT  *
        FROM    b_numbered
        PIVOT   (
                FOR  r_num  IN ( 2  AS qty_brk1
                               , 3  AS qty_brk2
                               , 4  AS qty_brk3
                               , 5  AS qty_brk4
                               )
                )
    )
    SELECT  b.*, a.qty_brk1, a.qty_brk2, a.qty_brk3, a.qty_brk4  -- or whatever columns you want
    FROM    table_a    a
    JOIN    b_pivoted  b  ON    a.part_no  = b.part_no
                          AND   (   DECODE (a.qty_brk1, b,qty_brk1, 1, 0) = 0)
                                OR  DECODE (a.qty_brk2, b,qty_brk2, 1, 0) = 0)
                                OR  DECODE (a.qty_brk3, b,qty_brk3, 1, 0) = 0)
                                OR  DECODE (a.qty_brk4, b,qty_brk4, 1, 0) = 0)
                                )
    ;
    

    I hope this answers your question.

    If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.  Include examples of all special cases you might need to handle, such as more breaks in one thble than in the other, part_no missing in either table, or duplicate part_no in table_a.

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

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

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

This discussion has been closed.