sql - Add amounts from two different tables Oracle -
i have 2 tables, interest table , charges table. interest table has debit interest , credit interest have been able difference using query:
select e.sol_id, (sum(case when e.interest_ind = 'd' e.amount_in_lcy else 0 end)- sum(case when e.interest_ind = 'c' e.amount_in_lcy else 0 end)) difference tbaadm.interest_details e group e.sol_id;
my output correct:
i need add amount in charges table have attempted this:
select e.sol_id, (sum(case when e.interest_ind = 'd' e.amount_in_lcy else 0 end)- sum(case when e.interest_ind = 'c' e.amount_in_lcy else 0 end) + sum(f.amount_in_lcy)) difference tbaadm.interest_details e,tbaadm.charge_details f f.sol_id = e.sol_id group e.sol_id, f.sol_id
this query after hanging moment gives me output:
which wrong considering while subtracting debits credit in first tables has entries branch 000,001 , 003, how can add amount second table , maintain result set if second table not have entries branch 000 , 003?
i think need outer join.
you have 2 datasets this:
set1: id val set2: id val => expected result: 1 10 1 5 1 15 2 20 2 20 3 30 3 30
so need right outer join
for sql be:
select e.sol_id ,(sum(case when e.interest_ind = 'd' e.amount_in_lcy else 0 end) - sum(case when e.interest_ind = 'c' e.amount_in_lcy else 0 end) + sum(f.amount_in_lcy) ) difference tbaadm.interest_details e ,tbaadm.charge_details f e.sol_id = f.sol_id (+) group e.sol_id, f.sol_id;
the (+) denotes table, can null. way write be:
select e.sol_id ,(sum(case when e.interest_ind = 'd' e.amount_in_lcy else 0 end) - sum(case when e.interest_ind = 'c' e.amount_in_lcy else 0 end) + sum(f.amount_in_lcy) ) difference tbaadm.interest_details e left outer join tbaadm.charge_details f on e.sol_id = f.sol_id group e.sol_id, f.sol_id;
the second version conforming sql standard, whereas first 1 (+) supported oracle.
Comments
Post a Comment