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
822 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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