How to compare complete sales orders against each other to look for differences?

I'm after some advice after hitting a brick wall. I don't have specific errors or problems in my script - I'm just not sure how to approach the problem after many hours of researching and trial and error.

I've been asked to create a script that identifies whether a whole order is duplicated under another order number. It is classed as a duplicate if the customer reference, all items and their respective quantities are the same, but with a difference order number.

Based on the table below, the expected outcome is for both of these orders to show on the report because although the order numbers are different, the customer reference, items and quantities are identical.

Customer Reference	Order	Position	Item	Quantity
AAA123	001	1	Apples	12
AAA123	001	2	Oranges	3
AAA123	001	3	Pears	9
AAA123	001	4	Grapes	18
AAA123	002	1	Apples	12
AAA123	002	2	Oranges	3
AAA123	002	3	Pears	9
AAA123	002	4	Grapes	18

I have been successful in creating a report that checks for any item and quantity ordered under the same customer reference on a different order number, but this isn't helpful because we have lots of blanket orders, where a customer places one PO at the start of the year and uses it each time they place an order. This is why I've been asked to check whether the whole orders are identical - we don't ever see that.

Any advice you could give would be really appreciated. We use Oracle 11.2.0.3.

Thanks in advance.

Solution

If the ORDER_IDs is what you want (Based on the table below, the expected outcome is for both of these orders to show on the report), then you could just self join the table on all columns equal except ORDER_ID which should be not equal:

WITH
    tbl (CUSTOMER_REFERENCE, ORDER_ID, POSITION, ITEM, QUANTITY) AS
        (
            Select 'AAA123',    '001',  1,  'Apples',   12 From Dual Union All
            Select 'AAA123',    '001',  2,  'Oranges',   3 From Dual Union All
            Select 'AAA123',    '001',  3,  'Pears',     9 From Dual Union All
            Select 'AAA123',    '001',  4,  'Grapes',   18 From Dual Union All
            Select 'AAA123',    '002',  1,  'Apples',   12 From Dual Union All
            Select 'AAA123',    '002',  2,  'Oranges',   3 From Dual Union All
            Select 'AAA123',    '002',  3,  'Pears',     9 From Dual Union All
            Select 'AAA123',    '002',  4,  'Grapes',   18 From Dual
        )
Select      t.ORDER_ID
From        tbl t
Inner Join  tbl t1 ON(t1.CUSTOMER_REFERENCE = t.CUSTOMER_REFERENCE And t1.POSITION = t.POSITION And 
                      t1.ITEM = t.ITEM And t1.QUANTITY = t.QUANTITY And t1.ORDER_ID != t.ORDER_ID)
Group By    t.ORDER_ID
Order By    t.ORDER_ID

ORDER_ID
--------
001      
002

... And if you want the complete data for both orders matching the condition then:

Select      t.*
From        tbl t
Inner Join  tbl t1 ON(t1.CUSTOMER_REFERENCE = t.CUSTOMER_REFERENCE And t1.POSITION = t.POSITION And 
                      t1.ITEM = t.ITEM And t1.QUANTITY = t.QUANTITY And t1.ORDER_ID != t.ORDER_ID)
Order By    t.ORDER_ID, t.POSITION

CUSTOMER_REFERENCE ORDER_ID   POSITION ITEM      QUANTITY
------------------ -------- ---------- ------- ----------
AAA123             001               1 Apples          12 
AAA123             001               2 Oranges          3 
AAA123             001               3 Pears            9 
AAA123             001               4 Grapes          18 
AAA123             002               1 Apples          12 
AAA123             002               2 Oranges          3 
AAA123             002               3 Pears            9 
AAA123             002               4 Grapes          18