Solved

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

Posted on 2007-11-28
3
810 Views
Last Modified: 2008-02-01
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
Comment
Question by:ttinsley
3 Comments
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 100 total points
ID: 20367399
SELECT ISNULL(field,'This field is null') from table

this will let you replace NULLs with something else
0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 200 total points
ID: 20367436
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
 

Assisted Solution

by:rprasad_331
rprasad_331 earned 200 total points
ID: 20367462
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

947 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

21 Experts available now in Live!

Get 1:1 Help Now