CODEWITHSUNDEEP
sqloracle-database
Reuben
Reuben

Reputation: 25

Oracle Minus - From a list of values, how do I count ONLY non reversed values

I have a list of payment transactions in a table. That include payment reversals. The Payment reversals are not flagged, so I have to infer that if a deduction is equal to an existing transaction it is a reversal

I want a count of only the transactions that were not reversed.

E.g From a list of transactions amounts.

5 5 -5 (This is reversal for one 5 transaction, so one 5 transaction is still valid) 10 -10 (This is reversal for one 10 transaction, so no 10 transactions are valid ) 15 15 15 -15 (This is reversal for one 15 transaction, so two 15 transactions are valid ) 20

I want a list which is

5 15 15 20

i.e I had four transactions which were not reversed.

Here is some test code, my attempt did not work. 

CREATE TABLE check_minus
(
name_id NUMBER(4),
trans NUMBER(4),
val NUMBER(2)
);

insert into  check_minus values (1,1,5);
insert into  check_minus values (1,2,5);
insert into  check_minus values (1,3,-5);
insert into  check_minus values (1,4,10);
insert into  check_minus values (1,5,-10);
insert into  check_minus values (1,6,15);
insert into  check_minus values (1,7,15);
insert into  check_minus values (1,8,15);
insert into  check_minus values (1,9,-15);
insert into  check_minus values (1,10,20);
commit;

-- using  not in
select name_id, val from check_minus
    where val > 0  -- positive transactions
    and (name_id, val) not in 
    (
    select name_id, abs(val) val from check_minus
    where val < 0 -- negative transactions
    );

-- using  minus
select name_id, val from check_minus
    where val > 0  -- positive transactions
 minus 
select name_id, abs(val) from check_minus
    where val < 0  -- negative transactions

Upvotes: 1

Views: 1248

Answers (1)

LBushkin
LBushkin

Reputation: 131786

Minus operations use distinct sets. Try this instead:

select row_number() over (partition by name_id, val order by name_id, val), name_id, val 
from check_minus
where val > 0
  minus
select row_number() over (partition by name_id, val order by name_id, val), name_id, abs(val) 
from check_minus
where val < 0

It produces:

RowNum Name_Id   Val
1,     1,        20
2,     1,        5
2,     1,        15
3,     1,        15

Upvotes: 3

Related Questions