Solved

SQL CASE field WHEN NULL then... not returning data

Posted on 2003-11-06
4
2,338 Views
Last Modified: 2012-06-21
In View SQL statement:

SELECT     TR_ID, TR_Name, TR_Qty, TR_CalcQty, CASE TR_Qty WHEN NULL THEN TR_CalcQty ELSE TR_Qty END AS New_Qty
FROM         dbo.tblTR

this doesn't recognise that records are null

How can I fix this?
0
Comment
Question by:Doozy
4 Comments
 
LVL 58

Accepted Solution

by:
amit_g earned 250 total points
ID: 9698242
SELECT     TR_ID, TR_Name, TR_Qty, TR_CalcQty, IsNull(TR_Qty, TR_CalcQty) AS New_Qty
FROM         dbo.tblTR
0
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9698705
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9699166
if you prefer your original query not to be changed:

try this before you run your query:

SET ANSI_NULLS OFF

go

OR

sp_db_options "<your_database_name>" , "ANSI nulls" , false
go
0
 

Expert Comment

by:synakamr
ID: 14870121
Change:
SELECT     TR_ID, TR_Name, TR_Qty, TR_CalcQty, CASE TR_Qty WHEN NULL THEN TR_CalcQty ELSE TR_Qty END AS New_Qty
FROM         dbo.tblTR

To:
SELECT     TR_ID, TR_Name, TR_Qty, TR_CalcQty, CASE WHEN TR_Qty IS NULL THEN TR_CalcQty ELSE TR_Qty END AS New_Qty
FROM         dbo.tblTR

Detail:
CASE TR_Qty WHEN NULL
CASE WHEN TR_Qty IS NULL
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

680 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