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
820 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 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