Search code examples
sqlsql-serverjoinsql-updatecoalesce

Coalesce not working in an update join

I have an update query where I am trying to find the net interest from multiple accounts on three different transaction tables deposits, checks, and general entries. The problem I'm running into is when one of those tables has no entries for a loan number to an interest account the select returns nothing and subsequently displays a null in the production table too. Negating the other tables if they return rows from interest account. If all three tables have a row for interest the query works fine.

Here's the code:

UPDATE Prod
  SET Prod.InterestSpread = (T1.Amount+T2.Amount-T3.Amount)
FROM dbo.Production_Pipeline as Prod
LEFT JOIN
(
  SELECT LoanNumber,COALESCE(SUM(JournalLineAmount),0)as Amount
  FROM dbo.GeneralLedger
  WHERE (JournalLineAccountRefListID = 'BB0000-1103842703' 
  OR JournalLineAccountRefListID = '800001DA-1202763722')
) AS T1
ON T1.LoanNumber = Prod.LoanNumber
LEFT JOIN
(
  SELECT LoanNumber,COALESCE(SUM(DepositLineAmount),0)as Amount
  FROM dbo.DepositLedger
  WHERE ( DepositLineAccountRefListID = 'BB0000-1103842703' 
  OR DepositLineAccountRefListID = '800001DA-1202763722')
) AS T2
ON T2.LoanNumber = Prod.LoanNumber
LEFT JOIN
(
  SELECT LoanNumber,COALESCE(SUM(ExpenseLineAmount),0) AS Amount
  FROM dbo.CheckLedger
  WHERE(ExpenseLineAccountRefListID = '800002B4-1308771936'
    OR ExpenseLineAccountRefListID = 'D30000-1105022008'
    OR ExpenseLineAccountRefListID = '8000029E-1283179936'
    OR ExpenseLineAccountRefListID = 'BB0000-1103842703'
    OR ExpenseLineAccountRefListID = '800001DA-1202763722')
) AS T3
ON T3.LoanNumber = Prod.LoanNumber

So an inccorect result update looks like
T1Amount: 496.08
T2Amount:
T3Amount: 373.92
Interest Spread: NULL

I've done some quite a bit of research on this site but, have been unable to apply your wisdom to my specific issue so any help here would greatly be appreciated.

Solution

  • Move your COALESCE statement to outside of the JOIN and remove it from the subqueries:

    UPDATE Prod
SET Prod.InterestSpread =
      COALESCE(T1.Amount,0)+COALESCE(T2.Amount,0)-COALESCE(T3.Amount,0)
...

    The problem is your subqueries return no results, thus coalesce inside of that cannot be applied to the field. By using it outside of the outer join, you ensure if the result is null, it converts properly.