title: finding the difference of the sum of a column values based on an indicator(a/b) column in a table
body: <pre><code>select ca.cust_ac_no, ca.ccy, ah.trn_dt,
  (SELECT  sum(COALESCE(hi.lcy_amount,0))                                    
       FROM actb_history hi 
       WHERE hi.ac_no='0013001600038'  and hi.drcr_ind = 'C' and ah.trn_dt = hi.trn_dt
       GROUP BY hi.ac_no,hi.drcr_ind) as Total_Credits,
  (SELECT  sum(COALESCE(hi.lcy_amount,0))                                    
       FROM actb_history hi 
       WHERE hi.ac_no='0013001600038'  and hi.drcr_ind = 'D'and ah.trn_dt = hi.trn_dt
       GROUP BY hi.ac_no,hi.drcr_ind) as Total_Debits,
  
  ((SELECT  sum( COALESCE(hi.lcy_amount,0))                                    
       FROM actb_history hi 
       WHERE hi.ac_no='0013001600038'  and hi.drcr_ind = 'C' and ah.trn_dt = hi.trn_dt
       GROUP BY hi.ac_no,hi.drcr_ind)
       -
  (SELECT  sum(COALESCE(hi.lcy_amount,0))                                    
       FROM actb_history hi 
       WHERE hi.ac_no='0013001600038'  and hi.drcr_ind = 'D'and ah.trn_dt = hi.trn_dt
       GROUP BY hi.drcr_ind,hi.drcr_ind )) as difference
  
  from actb_history ah, sttm_cust_account ca
  where ah.ac_no='0013001600038'
  and ah.ac_no = ca.cust_ac_no
  group by ca.cust_ac_no,  ca.ccy, ah.trn_dt
  </code></pre>
  
  <p>The Above code calculates the difference only where there are corresponding values for total_credits and total_debits</p>
  
  <p>The formula is credit-debit= difference. The problem is some transactions have only one and the other is null. so the result is null after adding the nulls. </p>
  
  <p>My desire is that it should display zero for any null value and be able to carry out the subtraction.</p>
  
  <p>Please help.</p>