CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Chris Pop
Chris Pop

Reputation: 27

Compare Two Different Fields In Oracle SQL

I have a requirement in which I have two main fields Amount CR and Amount DR. Now the requirement is that this both amounts have different values like Trx Number, Bank Name ETC but have a common Reference Number. There is only one record for every Refrence Number with a CR Amount, DR Amount respectivly.

For detaila see the table below:

Transaction Number Bank Name Reference Number CR Amount DR Amount
1 XYZ 1234 1000
2 ABC 1234 1000
3 DEF 1111 1000
4 TEST 1111 2300

So basically I want to compare CR and DR Amount based on the Reference Number. In the example Reference Number 1234 is ok and Reference Number 1111 should be listed.

How can I achieve this by an Oracle query?

Upvotes: 1

Views: 394

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21063

Add two analytical aggregated functions calculating the sum of CRand DB per the reference_number and compare them

case when
sum(cr_amount) over (partition by reference_number) =
sum(dr_amount) over (partition by reference_number) then 'Y' else 'N' end is_equ

This identifies the rows with reference_number where the sum is not equal.

In an additional query simple filter only the rows where the not equal sum.

with test as (
select a.*,
case when 
sum(cr_amount) over (partition by reference_number) =
sum(dr_amount) over (partition by reference_number) then 'Y' else 'N' end is_equ
from tab a)
select 
  TRANSACTION_NUMBER, BANK_NAME, REFERENCE_NUMBER, CR_AMOUNT, DR_AMOUNT
from test 
where is_equ = 'N'

TRANSACTION_NUMBER BANK REFERENCE_NUMBER  CR_AMOUNT  DR_AMOUNT
------------------ ---- ---------------- ---------- ----------
                 3 DEF              1111                  1000
                 4 TEST             1111       2300

Upvotes: 0

Turo
Turo

Reputation: 4914

Knowing that there is exactly one record with dr and one with cr amount you can make a self join over the reference number. The 2 Trransactions for a Reference Number will be listed in one row:

select * from table t1 
 inner join table t2 on t1.referencenumber = t2.referencenumber 
                   and t1.cr_amount is not null
                   and t2.dr_amount is not null
where t1.cr_amount <> t2.dr_amount

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can aggregate and use a case expression:

select reference_number,
       sum(cr_amount), sum(db_amount),
       (case when sum(cr_amount) = sum(db_amount)
             then 'same' else 'different'
        end)
from t
group by reference_number;

Upvotes: 0

Related Questions