SQL View changing null fields to zero

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL View changing null fields to zero

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-13 : 10:51:12

In the script below, I want to change all fields that are null to zero except for debcode. This is a script I'm using to create a SQL view.


SELECT dbo.cicmpy.debcode,
       dbo.ARSRVCRCUSTMTD.MTDSALECR,
       dbo.ARSRVCRCUSTMTD.MTDCOSTCR,
       dbo.OESaleCostMTD.SaleMTD, 
       dbo.OESaleCostMTD.CostMTD, 
       dbo.OESaleCostMTDCR.SaleMTD AS SALECR,
       dbo.OESaleCostMTDCR.CostMTD AS CRCOST

FROM dbo.OESaleCostMTD RIGHT OUTER JOIN
     dbo.cicmpy ON dbo.OESaleCostMTD.cus_no = dbo.cicmpy.debcode 
LEFT OUTER JOIN
     dbo.OESaleCostMTDCR ON
     dbo.cicmpy.debcode=dbo.OESaleCostMTDCR.cus_no
FULL OUTER JOIN
     dbo.ARSRVCRCUSTMTD ON 
     dbo.cicmpy.debcode = dbo.ARSRVCRCUSTMTD.cust_no
WHERE(dbo.cicmpy.debcode IS NOT NULL)

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-13 : 10:54:49

figured it out.

SELECT dbo.cicmpy.debcode,
isnull(dbo.ARSRVCRCUSTMTD.MTDSALECR,0),
dbo.ARSRVCRCUSTMTD.MTDCOSTCR,
dbo.OESaleCostMTD.SaleMTD,
dbo.OESaleCostMTD.CostMTD,
dbo.OESaleCostMTDCR.SaleMTD AS SALECR,
dbo.OESaleCostMTDCR.CostMTD AS CRCOST

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-13 : 10:57:31

use ISNULL() or COALESCE() function

SELECT dbo.cicmpy.debcode,
       isnull(dbo.ARSRVCRCUSTMTD.MTDSALECR, 0) MTDSALECR,
       isnull(dbo.ARSRVCRCUSTMTD.MTDCOSTCR, 0) MTDCOSTCR,
       isnull(dbo.OESaleCostMTD.SaleMTD, 0) SaleMTD,
       isnull(dbo.OESaleCostMTD.CostMTD, 0)  CostMTD,
       isnull(dbo.OESaleCostMTDCR.SaleMTD, 0) SALECR,
       isnull(dbo.OESaleCostMTDCR.CostMTD, 0) CRCOST
FROM dbo.OESaleCostMTD RIGHT OUTER JOIN
     dbo.cicmpy ON dbo.OESaleCostMTD.cus_no = dbo.cicmpy.debcode 
LEFT OUTER JOIN
     dbo.OESaleCostMTDCR ON
     dbo.cicmpy.debcode=dbo.OESaleCostMTDCR.cus_no
FULL OUTER JOIN
     dbo.ARSRVCRCUSTMTD ON 
     dbo.cicmpy.debcode = dbo.ARSRVCRCUSTMTD.cust_no
WHERE(dbo.cicmpy.debcode IS NOT NULL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources