CODEWITHSUNDEEP
sqlsql-servernullcoalesce
pgunston
pgunston

Reputation: 302

How can I use 0 instead of NULL in SQL Server

Check out the query below: 

WITH TEMP([Company], [Company Name], [GL Description], [Amount], [BeginBal])
AS
(
    SELECT 
       GLAC.GLCo AS Company, 
       HQCO.Name AS 'Company Name', 
       GLAC.Description AS 'GL Description', 
       SUM(GLDT.Amount) AS Amount, 
       GLYB.BeginBal 
    FROM 
       GLAC 
    LEFT JOIN 
       HQCO ON GLAC.GLCo = HQCO.HQCo
    LEFT JOIN 
       GLDT ON GLAC.GLCo = GLDT.GLCo AND GLAC.GLAcct = GLDT.GLAcct
    LEFT JOIN 
       GLYB ON GLAC.GLCo = GLYB.GLCo AND GLAC.GLAcct = GLYB.GLAcct
    WHERE 
       GLAC.udCategory = 'Cash At Bank' 
       AND GLAC.Active = 'Y'  
       AND (GLAC.GLCo = 1 or GLAC.GLCo = 5 or GLAC.GLCo = 6 or GLAC.GLCo = 7) 
       AND GLYB.FYEMO = '2012-06-01 00:00:00' 
    GROUP BY
       GLAC.GLCo, HQCO.Name , GLAC.Description, GLYB.BeginBal
)
SELECT
    Company, [Company Name], [GL Description], Amount + BeginBal as 'Balance' 
FROM 
    TEMP

This produces the below table:

 +---------+----------------------+------------------------------+-------------+
| Company |     Company Name     |        GL Description        |   Balance   |
+---------+----------------------+------------------------------+-------------+
|       1 | Contracting Pty Ltd  | Cash At Bank ANZ Cheque a/c  | -8423347.81 |
|       1 |  Contracting Pty Ltd | Investment Online Saver Acct | 15040000    |
|       1 |  Contracting Pty Ltd | Westpac Bonus Cash Reserve   | 123133      |
|       5 | ABC UTC PTY LTD      | Cash At Bank ANZ Cheque a/c  | 13121902.53 |
|       6 | ABC UTC PTY LTD      | Cash At Bank ANZ Cheque a/c  | 3932872.45  |
|       6 | ABC UTC PTY LTD      | Investment Online Saver Acct | 142448      |
|       7 |  Plant Hire Pty Ltd  | Cash At Bank ANZ Cheque a/c  | 253253.32   |
+---------+----------------------+------------------------------+-------------+

Some of my data is missing and this is because of this line:

GLYB.FYEMO = '2012-06-01 00:00:00'

The problem is that some records in 'GLAC' do not have a value in GLYB therefore, the null value does not appear in my table.

How can I make all records appear if any of these conditions apply:

Upvotes: 0

Views: 89

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think you need to move the condition to the on clause, because the where turns the outer join to an inner join:

WITH TEMP([Company], [Company Name], [GL Description], [Amount], [BeginBal]) AS (
      select GLAC.GLCo as Company, HQCO.Name as [Company Name], GLAC.Description as [GL Description], 
             SUM(GLDT.Amount) as Amount, GLYB.BeginBal
      from GLAC LEFT JOIN
           HQCO
           ON GLAC.GLCo = HQCO.HQCo LEFT JOIN
           GLDT
           ON GLAC.GLCo = GLDT.GLCo and GLAC.GLAcct = GLDT.GLAcct LEFT JOIN
           GLYB
           ON GLAC.GLCo = GLYB.GLCo and
              GLAC.GLAcct = GLYB.GLAcct and
              GLYB.FYEMO = '2012-06-01 00:00:00' 
      where GLAC.udCategory = 'Cash At Bank' and GLAC.Active = 'Y'  and
            (GLAC.GLCo in (1, 5, 6, 7)
      Group By GLAC.GLCo, HQCO.Name , GLAC.Description, GLYB.BeginBal
     )
Select Company, [Company Name], [GL Description], Amount + BeginBal as Balance
from TEMP;

Upvotes: 1

Related Questions