Case Statement in View in SQL Server Management Studio/sql server 2012 1

[Tarnish]

Hi,

I'm new to sql server and I'm using sql server management studio to help me convert some queries for use on sql server 2012 in the form of views. I'm trying to use functions to create some columns in the view, and none of them seem to work properly.

Specifically, this fails:

CASE RecipientID WHEN NULL THEN 0 ELSE 1 End

That results always in 1 being the value of the column. The RecipientID value has some nulls but mostly has values (datatype integer). Every time I try to use case it falls to the 'else' part of the statement for the value, regardless of how simple or complex the options are. I thought about maybe it being some behind the scenes datatype conversion issue?

The IIF statement never works either. Usually it tries to wrap my condition in single quotes and then says it's not a boolean value (error).

Is there some sql server setting that is required to use these logical statements in views? Is there some 'gotcha' that would explain why Case statements always fall to the else? I have tried various references to the data (RecipientID, Mail.RecipientID, dbo.Mail.RecipientID in the example) with no improvement.

Thanks for any direction. I've spent HOURS trying to figure this out.

T

 

[gmmastros]

Try this:

Case When RecipientId Is NULL Then 0 Else 1 End

The problem with your original code is that SQL Server was trying to check if the column is = null, which does not compare because nothing compares equal to null. Specifically, SQL Server was interpreting your statement as:

Case When RecipeintId = NULL Then 0 Else 1 End

Recipent = null would never work, so the code was always dropping in to the else statement.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

To further clarify my point, open a new query window and copy/paste this code:

Declare @Blah Int
Set @Blah = NULL

Select Case @Blah When NULL Then 0 Else 1 End,
       Case When @Blah = NULL Then 0 Else 1 End,
       Case When @Blah Is NULL Then 0 Else 1 End

When you run this code, you will see that the first 2 columns result in 1. The last column, with the "@Blah Is NULL" condition is the only one that results in 0 (as expected).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Tarnish]

Thanks for the quick reply!

That works, but I'm a little confused still. Is the problem specifically related to my use of Null? I thought if I put a column name after Case (without anything else like I did above) that I could then use potential values after 'when' keyword. I saw an example like this:

Case productline
When 'r' Then 'Something'
When 'p' Then 'something else'
When 'g' Then 'another something'
Else 'something else altogether'
End

Thanks a lot for your help. I still have some hair left!

T

 

[gmmastros]

You example works. However, when you want to check for NULL, you must use the other form of the case statement:

Case 
When productline = 'r' Then 'Something'
When productline = 'p' Then 'something else'
When productline = 'g' Then 'another something'
When productline is NULL then 'This one is null'
Else 'something else altogether'
End

It all has to do with the way the case statement is processed by the SQL database engine, and the way that NULLs are compared. You use [!]Column Is NULL[/!] when checking for NULL values.

There are other ways to handle this too, but they all include some sort of null checking.

Case [!]Coalesce(productline, '')[/!]
When 'r' Then 'Something'
When 'p' Then 'something else'
When 'g' Then 'another something'
When [!]''[/!] then 'This one is null'
Else 'something else altogether'
End

Often times it helps to think of NULL as "unknown". Imagine you have a table of people, with an EyeColor column.

This would return people with brown eyes:

Select *
From   People 
Where  EyeColor = 'Brown'

This would return people with any other color eyes:

Select *
From   People 
Where  EyeColor [!]<>[/!] 'Brown'

This most recent query would NOT return people with NULL because some of the people MAY have brown eyes. The NULL means, unknown, so you cannot include NULLs, even in a not equal check because you simply don't know if they have brown eyes, or any other color eyes.

NULL is a rather abstract concept that takes a little while to get used to. I encourage you to make sure you understand it thoroughly. If there's any part of NULL that confuses you, let me know and I will explain it the best that I can.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Tarnish]

Thanks a lot for your great responses. I'm all set now!

T