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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
while loop in html mail format 5 34
Oracle - Create Procedure with Paramater 16 32
Update in Sql 7 8
Query 14 19
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

13 Experts available now in Live!

Get 1:1 Help Now