SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CASE statement and NULL values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlslick
Yak Posting Veteran

83 Posts

Posted - 10/22/2013 :  15:18:59  Show Profile  Reply with Quote
Hi again,

Please see code below:

SELECT
NAME,
CASE WHEN PHONE IS NULL THEN '-' ELSE PHONE END AS PHONE
FROM
PATIENT
WHERE PHONE = '-'

If I am switching the NULL values to hyphens (-), why am I not getting any records when I look for hyphens in the WHERE clause? The script is not as simple as this or else I would just check for NULL values. I really need to switch NULL values to something I can pass on to a variable and query on it from Reporting Services.

Thank you all!

tkizer
Almighty SQL Goddess

USA
36674 Posts

Posted - 10/22/2013 :  15:49:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
Your "WHEN PHONE IS NULL" is conflicting with your "WHERE PHONE = '-'". Remove the WHERE clause.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

323 Posts

Posted - 10/22/2013 :  16:15:01  Show Profile  Reply with Quote
Problem here is most likely, you are trying to use where clause on field alias. This can (afaik) not be done, and normally you would get an error. You didn't get an error, as you named the alias, same as the field, and thus the where clause is evaluating on the field, not the alias.
In your ex, you can write your where clause like this:
where phone is null or phone='-'
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 10/22/2013 :  21:35:03  Show Profile  Reply with Quote
kindly refer to this
http://msdn.microsoft.com/en-us/library/ms189499.aspx

[select] part of query is executed after [where] part, therefore in [where] part, you cant use any of the column you created in [select] part.

you can try

SELECT	*
FROM	(
		SELECT
			NAME,
			CASE WHEN PHONE IS NULL THEN '-' ELSE PHONE END AS PHONE
		FROM PATIENT
		)src
WHERE PHONE = '-'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/23/2013 :  01:36:56  Show Profile  Reply with Quote
can be simplified as

SELECT
			NAME,
			COALESCE(PHONE,'-') AS PHONE
		FROM PATIENT
WHERE COALESCE(PHONE,'-') = '-'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 10/23/2013 :  08:47:33  Show Profile  Reply with Quote
Great, thanks for the tips everyone! Much appreciated!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 10/24/2013 :  04:01:15  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
If there is an index on PHONE column you may consider using

SELECT
			NAME,
			COALESCE(PHONE,'-') AS PHONE
		FROM PATIENT
WHERE PHONE is null or PHONE = '-'



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000