CASE statement and NULL values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CASE statement and NULL values

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2013-10-22 : 15:18:59

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

38200 Posts

Posted - 2013-10-22 : 15:49:54

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/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-10-22 : 16:15:01

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='-'

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-22 : 21:35:03

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 = '-'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 01:36:56

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

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2013-10-23 : 08:47:33

Great, thanks for the tips everyone! Much appreciated!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-24 : 04:01:15

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources