Solved

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

Posted on 2003-11-06
4
2,332 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:dishanf
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 36
Backing up an SQL Transaction Log 11 44
Query Peformance + mulitple query plans 9 48
Need SQL that flips  value 5 21
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

863 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

19 Experts available now in Live!

Get 1:1 Help Now