Case When with IS NULL - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Case When with IS NULL

Author

Topic

Karen426
Starting Member

3 Posts

Posted - 2008-11-12 : 17:42:12

Hi. I was hoping someone could help me. I have the following query where I am trying to get a count for each education level. It gives me all my results except where the field is Null or blank. In the Unknown column I should have a count of 174. I have listed my query and the results. I am sure I am just missing something but can't see it. Any help would be appreciated. Thanks.

USE [Warehouse]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

BEGIN
SET NOCOUNT ON;
IF Exists(select name
from [tempdb].[dbo].[Sysobjects]
where name like '#Education%')
begin
drop table #Education
end
create Table #Education
(
RowName varchar(50),
OrderNumber int
)

Insert Into #Education
values ('Less than grade 9', 1)
Insert Into #Education
values ('Grades 9 to 11, no diploma', 2)
Insert Into #Education
values ('High school diploma', 4)
Insert Into #Education
values ('GED', 3)
Insert Into #Education
values ('Some college or university', 5)
Insert Into #Education
values ('College or university degree', 6)
Insert Into #Education
values ('Graduate Degree', 7)
Insert Into #Education
values ('Do not wish to answer', 8)
Insert Into #Education
values ('Unknown', 9)

select OrderNumber,
case when RowName = 'Do not wish to answer' then 'Refuse'
when RowName is null then 'Unknown'
else RowName end as [Education],

(select count(*)
from obj_person a
where
RowName = EducationalAttainmentTypeName
and d.startdate >= '10/01/2008'
and d.startdate <= '10/31/2008'
)

from #Education
group by RowName, OrderNumber
order by OrderNumber
DROP TABLE #Education

END

Results:
OrderNumber Education Count
1 Less than grade 9 25
2 Grades 9 to 11, no diploma 15
3 GED 8
4 High school diploma 56
5 Some college or university 107
6 College or university degree 196
7 Graduate Degree 0
8 Refuse 0
9 Unknown 0

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-11-12 : 18:25:16

Change
RowName = EducationalAttainmentTypeName
to
RowName = IsNull(EducationalAttainmentTypeName, 'Unknown')

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 23:19:34

quote:
Originally posted by jhocutt

Change
RowName = EducationalAttainmentTypeName
to
RowName = IsNull(EducationalAttainmentTypeName, 'Unknown')

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

better to use coalesce instead of IsNull. see below

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/04/isnull-or-coalesce.aspx

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-11-12 : 23:47:37

Better is subjective, IsNull is faster and I know the limitations.
In my mind faster is better

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 23:55:24

quote:
Originally posted by jhocutt

Better is subjective, IsNull is faster and I know the limitations.
In my mind faster is better

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

even if it doesnt give you desired result always? then its upto u

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-13 : 01:47:25

quote:
Originally posted by jhocutt

Change
RowName = EducationalAttainmentTypeName
to
RowName = IsNull(EducationalAttainmentTypeName, 'Unknown')

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

declare @EducationalAttainmentTypeName char(1)
select IsNull(@EducationalAttainmentTypeName, 'Unknown')
select Coalesce(@EducationalAttainmentTypeName, 'Unknown')

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 02:07:38

quote:
Originally posted by madhivanan

quote:
Originally posted by jhocutt

Change
RowName = EducationalAttainmentTypeName
to
RowName = IsNull(EducationalAttainmentTypeName, 'Unknown')

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

declare @EducationalAttainmentTypeName char(1)
select IsNull(@EducationalAttainmentTypeName, 'Unknown')
select Coalesce(@EducationalAttainmentTypeName, 'Unknown')

Madhivanan

Failing to plan is Planning to fail

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-11-13 : 09:23:45

:) Yes I understand that limitation.
And I make sure that the value I am returning is the same length or shorter than the variable, or column I am using.

Hmmm kinda like dont put 10 chars into a 5 char variable

declare @a varchar(5)
set @a = 'Hello World'
select @a

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 01:12:01

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-14 : 05:16:36

I've used ISNULL for ages without problems...and illustrating your advice by forcing a 7 character word into a 1 character variable is just insane. Which ones of these inserts fails?? ->

declare @EducationalAttainmentTypeName char(1)
declare @table table (
  EducationalAttainmentTypeName char(1)
)
insert into @table select IsNull(@EducationalAttainmentTypeName, 'Unknown')
insert into @table select Coalesce(@EducationalAttainmentTypeName, 'Unknown')  

select * from @table

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 05:25:20

Maybe a better example will convince someone?

DECLARE	@Table TABLE
	(
		name VARCHAR(100)
	)

DECLARE	@name VARCHAR(3)

INSERT	@Table
SELECT	ISNULL(@name, 'Unknown') UNION ALL
SELECT	COALESCE(@name, 'Unknown')  

SELECT	*
FROM	@Table

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-14 : 05:37:02

This is a far better example and illustrates the differences alot better. I'd still use ISNULL any day over COALESCE for these things but of course you need to have control over your datatypes/lengths.

And madhi; I just reread my post and I realised I sort of called you insane. Clearly that was not my intention at all, I just didn't like your example. I hope you were not offended

- Lumbago

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 07:32:10

<<
And madhi; I just reread my post and I realised I sort of called you insane. Clearly that was not my intention at all, I just didn't like your example. I hope you were not offended
>>

No problem

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 08:01:23

<<
I just didn't like your example.
>>

Consider other example

You have a table with column named status of char(1) datatype
What if you want to show 'Unknown' if status is NULL?
COALESCE is best suitable

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources