Compare and find differences in two tables in Oracle

I have 2 tables:

1. account: ID, ACC, AE_CCY, DRCR_IND, AMOUNT, MODULE
2. flex: ID, ACC, AE_CCY, DRCR_IND, AMOUNT, MODULE

I want to show differences comparing only by: AE_CCY, DRCR_IND, AMOUNT, MODULE and ACC by first 4 characters

Example:

ID ACC       AE_CCY DRCR_IND AMOUNT MODULE
-- --------- ------ -------- ------ ------
1  734647674 USD    D        100    OP

and in flex:

ID ACC       AE_CCY DR开发者_如何学GoCR_IND AMOUNT MODULE
-- --------- ------ -------- ------ ------
1  734647654 USD    D        100    OP
2  734665474 USD    D        100    OP
9  734611111 USD    D        100    OP

ID's 2 and 9 should be shown as differences.

If I use FULL JOIN I'll get no differences as substr(account.ACC,1,4) = substr(flex.ACC,1,4) are equal and others are equal and MINUS doesn't work because ID's different.

Do you mean you want to group by the first 4 characters of ACC, then diff them?

And, if not, why is Flex:ID=1 NOT a difference to account:ID=1, if ID=2 and ID=9 are, especially since it reads that ID is not a comparison field?

a brute-force set theory answer:

SELECT * FROM ID 
UNION  
SELECT * FROM FLEX 
MINUS 
  (SELECT * FROM ID 
   INTERSECT  
   SELECT * FROM FLEX)

I think what you want is the full join with an additional condition. Something like:

select F.ID, F.AE_CCY, F.DRCR_IND, F.AMOUNT, F.MODULE, F.ACC 
from account a join flex f 
  on substr(a.ACC,1,4) = substr(f.ACC,1,4)
where a.AE_CCY <> f.AE_CCY 
   or a.DRCR_IND <> f.DRCR_IND 
   or a.AMOUNT <> f.AMOUNT
   or a.MODULE <> f.MODULE
   or a.ACC <> f.ACC

This way, the join is still performed on the first 4 characters, but the where condition checks the entire field (as well as the other four).

Revised solution: This is something of a stab-in-the-dark, by I'm wondering if what you're really looking for is a list of records that don't have a match in the other table. In that case, a full outer join might be the answer:

select coalesce(F.ID,a.ID) as ID, 
       coalesce(F.AE_CCY,a.AE_CCY) as AE_CCY, 
       coalesce(F.DRCR_IND,a.DRCR_IND) as DRCR_IND, 
       coalesce(F.AMOUNT,a.AMOUNT) as AMOUNT, 
       coalesce(F.MODULE,a.MODULE) as MODULE, 
       coalesce(F.ACC,a.ACC) as ACC
from account a full outer join flex f 
  on substr(a.ACC,1,4) = substr(f.ACC,1,4)
     and a.AE_CCY = f.AE_CCY 
     and a.DRCR_IND = f.DRCR_IND 
     and a.AMOUNT = f.AMOUNT
     and a.MODULE = f.MODULE
where a.id is null
   or f.id is null

Third attempted solution: Thinking about it further, I think you're saying that you want each record from the first table to match to exactly one record in the second table (and vice-versa). That's a difficult problem because relational databases aren't really design work that way.

The solution below uses the full outer join again, to get only rows that don't appear in the other table. This time, we're adding ROW_NUMBER to assign a unique number to each member of a set of duplicate values found in either table. In the example from your comment, with 5 identical rows in one table and 1 of the same row in another, the first table will be numbered 1-5 and the second will be 1. Therefore, by adding that as a join condition, we assure that each row has only one match. The one flaw in this design is that a perfect match on ACC is not guaranteed to take precedence over another value. Making that work would be quite a bit more difficult.

select coalesce(F.ID,a.ID) as ID, 
       coalesce(F.AE_CCY,a.AE_CCY) as AE_CCY, 
       coalesce(F.DRCR_IND,a.DRCR_IND) as DRCR_IND, 
       coalesce(F.AMOUNT,a.AMOUNT) as AMOUNT, 
       coalesce(F.MODULE,a.MODULE) as MODULE, 
       coalesce(F.ACC,a.ACC) as ACC
from (select a.*, 
             row_number() 
             over (partition by AE_CCY,DRCR_IND,AMOUNT,MODULE,substr(ACC,1,4) 
                   order by acc) as rn 
      from account a) a 
     full outer join
     (select f.*, 
             row_number() 
             over (partition by AE_CCY,DRCR_IND,AMOUNT,MODULE,substr(ACC,1,4) 
                   order by acc) as rn 
      from flex f) f
     on substr(a.ACC,1,4) = substr(f.ACC,1,4)
     and a.AE_CCY = f.AE_CCY 
     and a.DRCR_IND = f.DRCR_IND 
     and a.AMOUNT = f.AMOUNT
     and a.MODULE = f.MODULE
     and a.RN = f.RN
where a.id is null
   or f.id is null

I like to use:

SELECT min(which) which, id, ae_ccy, drcr_ind, amount, module, acc
  FROM (SELECT DISTINCT 'account' which, id, ae_ccy, drcr_ind, amount, module, 
               substr(acc, 1, 4) acc
          FROM ACCOUNT
        UNION ALL
        SELECT DISTINCT 'flex' which, id, ae_ccy, drcr_ind, amount, module, 
               substr(acc, 1, 4) acc
          FROM flex)
 GROUP BY id, ae_ccy, drcr_ind, amount, module, acc
HAVING COUNT(*) != 2
 ORDER BY id, 1

It will show both the new rows, the old missing rows and any difference.