Need a bit guidance in how to merge two records and show the results based on that.
So here i want to show what actual Qty is transferred from one location to another and show result based on that.
Here we can show debit qty or credit qty in result set
create table t1 (id Varchar2(4), location_id number, location_name Varchar2(10),debit_qty number, credit_qty number, trans_id number, status varchar2(10));
insert into t1 values ('1', '1001', 'U.S.', 10 , 6,1001,'Trans');
insert into t1 values ('2', '1002', 'CANADA', 5 , 10,1001,'Trans');
insert into t1 values ('3', '1003', 'AUS', 20 , 4,1002,'Trans');
insert into t1 values ('4', '1004', 'IND', 8 , 20,1002,'Trans'); Desire output:
TID LOCATION_FROM DEBIT_QTY LOCATION_TO
1 U.S. 10 CANADA
2 AUS 20 IND
3 CANADA 20 IND
I'm not entirely sure I understand your question. If it is just a single from-to pairing then perhaps
SQL> create table t1 (id Varchar2(4), location_id number, location_name Varchar2(10),debit_qty number, credit_qty number, trans_id number, status varchar2(10));
Table created.
SQL>
SQL> insert into t1 values ('1', '1001', 'U.S.', 10 , 6,1001,'Trans');
1 row created.
SQL> insert into t1 values ('2', '1002', 'CANADA', 5 , 10,1001,'Trans');
1 row created.
SQL>
SQL> insert into t1 values ('3', '1003', 'AUS', 20 , 4,1002,'Trans');
1 row created.
SQL> insert into t1 values ('4', '1004', 'IND', 8 , 20,1002,'Trans');
1 row created.
SQL>
SQL> select *
2 from t1 t_from, t1 t_to
3 where t_from.trans_id = t_to.trans_id
4 and t_from.id < t_to.id;
ID LOCATION_ID LOCATION_N DEBIT_QTY CREDIT_QTY TRANS_ID STATUS ID LOCATION_ID LOCATION_N DEBIT_QTY CREDIT_QTY TRANS_ID STATUS
---- ----------- ---------- ---------- ---------- ---------- ---------- ---- ----------- ---------- ---------- ---------- ---------- ----------
1 1001 U.S. 10 6 1001 Trans 2 1002 CANADA 5 10 1001 Trans
3 1003 AUS 20 4 1002 Trans 4 1004 IND 8 20 1002 Trans
2 rows selected.
If it is a hierarchy of pairings, you'd need to tell us what the linkage is between each row, for example:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:489772591421