Solved

MS SQL 2000 Replace NULL is results

Posted on 2008-10-23
4
203 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:iepaul
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
yes, using ISNULL()
SELECT     TOP 100 PERCENT dbo.tblHeadCount.fldCostCentre, dbo.tblHeadCount.fldDate, dbo.tblHeadCount.fldHeadCount, 
                      ISNULL(dbo.tblLeavers.fldHeadCount,0) 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

Open in new window

0
 
LVL 6

Expert Comment

by:openshac
Comment Utility

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

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

0
 

Author Closing Comment

by:iepaul
Comment Utility
Thanks for the help and the tip on using table alias
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now