asked on

MS SQL 2000 Replace NULL is results

I have a view in MS SQL 2000 which is working and showing results. There is one issue though, if there is no value for dbo.tblLeavers.fldHeadCount it shows as NULL in the results. Can I replace this with a 0 in the SELECT statment.

SELECT TOP 100 PERCENT dbo.tblHeadCount.fldCostCentre, dbo.tblHeadCount.fldDate, dbo.tblHeadCount.fldHeadCount,
dbo.tblLeavers.fldHeadCount AS fldLeavers
FROM dbo.tblHeadCount LEFT OUTER JOIN
dbo.tblLeavers ON dbo.tblHeadCount.fldCostCentre = dbo.tblLeavers.fldCostCentre AND dbo.tblHeadCount.fldDate = dbo.tblLeavers.fldDate
WHERE (LEN(dbo.tblHeadCount.fldCostCentre) > 1)
ORDER BY dbo.tblHeadCount.fldDate DESC

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

openshac

ISNULL( dbo.tblLeavers.fldHeadCount, 0) AS fldLeavers

Open in new window

Guy Hengel [angelIII / a3]

side note: try to use table alias names:

SELECT     TOP 100 PERCENT hc.fldCostCentre, hc.fldDate, hc.fldHeadCount, isnull(l.fldHeadCount,0) AS fldLeavers
FROM         dbo.tblHeadCount hc 
LEFT OUTER JOIN dbo.tblLeavers l
  ON hc.fldCostCentre = l.fldCostCentre 
 AND hc.fldDate = l.fldDate
WHERE     (LEN(hc.fldCostCentre) > 1)
ORDER BY hc.fldDate DESC

Open in new window

iepaul

ASKER

Thanks for the help and the tip on using table alias