Solved

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

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 30
SqlServer no dupes 25 34
SSRS 2013 - Overlapping reports 2 20
optimize stored procedure 6 24
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

776 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