• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 829
  • Last Modified:

How do I replace a returned Null value with another value from a SQL Server Query based on an outer Join?

I am working with SQL Server 2000 DB and using SSMS studio for my design tool. Necessarily I have an Outer Join based Query that returns NULL values just as you expect it should. What I would like to do is return a different value such as 0 instead of the Null Value.

Background:
I am using Excel 2007 pivottables with a separate table page containing the returned results of the Query. I add formulas to the table page before summarizing with the PivotTables giving me alot more flexibility than returning results straight into the PivotTable. I would likke to keep this particular table page clean without NULLs and I think it would be much cleaner if I could prevent the Nulls from being returned to the table in the first place.

Code Example: The code below is an example using the AdventureWorks sample DB. This statement is sometimes returning a Null for the MaxQty column. I would like to replace the Nulls with a 0 but not touch the other MaxQty values returned.
USE AdventureWorks
GO
 
SELECT     Sales.SpecialOfferProduct.SpecialOfferID, Sales.SpecialOfferProduct.ProductID,  Sales.SpecialOffer.MaxQty
FROM         Sales.SpecialOfferProduct LEFT OUTER JOIN
                      Sales.SpecialOffer ON Sales.SpecialOfferProduct.SpecialOfferID = Sales.SpecialOffer.SpecialOfferID

Open in new window

0
ttinsley
Asked:
ttinsley
3 Solutions
 
James MurrellProduct SpecialistCommented:
SELECT ISNULL(field,'This field is null') from table

this will let you replace NULLs with something else
0
 
Chris MangusDatabase AdministratorCommented:
USE AdventureWorks
GO
 
SELECT     Sales.SpecialOfferProduct.SpecialOfferID, Sales.SpecialOfferProduct.ProductID,  Case When Sales.SpecialOffer.MaxQty Is Null Then 0 Else Sales.SpecialOffer.MaxQty End
FROM         Sales.SpecialOfferProduct LEFT OUTER JOIN
                      Sales.SpecialOffer ON Sales.SpecialOfferProduct.SpecialOfferID = Sales.SpecialOffer.SpecialOfferID
 
0
 
rprasad_331Commented:
Please use ISNULL function on the Sales.SpecialOffer.MaxQty column.
Syntax of ISNULL:
ISNULL(Column1, VALUE TO BE RETURNED IFNULL )

Hence the following modification in your query will return 0, if maxqty is null
ISNULL(Sales.SpecialOffer.MaxQty column, 0) MaxQty
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now