Hi,
I am facing an issue. The sample details are:
Create table t
(
item_id number(10),
je_name varchar2(50),
voucher_ref varchar2(50),
amount number(15,2));
Begin
Insert into t values (301, 'XE', 'XORSYS', -20);
Insert into t values (302, 'XE', 'XORSYS', 20);
Insert into t values (303, 'XE', 'XORSYS', 20);
Insert into t values (304, 'XE', 'XORSYS', 20);
Insert into t values (305, 'XE', 'XORSYS', -120);
Insert into t values (306, 'XE', 'XORSYS', 120);
Insert into t values (307, 'XE', 'XORSYS', 120);
Insert into t values (308, 'ZE', 'XORSYS', 120);
Insert into t values (309, 'ZE', 'XORSYS', -1120);
Insert into t values (310, 'ZE', 'XORSYS', 1120);
End;
select a.item_id,b.item_id
from t a,t b
where a.je_name = b.je_name
and a.voucher_ref = b.voucher_ref
and a.AMOUNT = (-1)*(b.AMOUNT)
order by a.item_id,b.item_id;
The requirement is that the amount of one item_id is to be adjusted with another item_id (for the same je_name and Voucher_ref). The result set should have both item ids. I tried self join but it is giving multiple rows (one item_id getting adjusted with multiple ids, it should adjust with just one). The table has lacs of records. Please help in resolving this.
TIA