What am I missing? - 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

General SQL Server Forums

New to SQL Server Programming

What am I missing?

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-27 : 10:56:39

This works:


select n.regionacronym, n.doc_name,  count(pending) as pending,  p.doc,
area = case when area is null then 'CLMS UN' else (area), dist = case when dist is null then 'CLMS UN' else (dist) end
from pdf p 
left join natdocfile n on n.doc = p.doc
group by  n.dist, n.area, n.regionacronym, n.doc_name, p.doc 
order by n.area

When I add the dist line


select n.regionacronym, n.doc_name,  count(pending) as pending,  p.doc,
area = case when area is null then 'CLMS UN' else (area), 

dist = case when dist is null then 'CLMS UN' else (dist) end
from pdf p 
left join natdocfile n on n.doc = p.doc
group by  n.dist, n.area, n.regionacronym, n.doc_name, p.doc
order by n.area

I get Incorrect syntax near ','
It's referring to the bold section

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-27 : 11:01:46

Never mind I figured it out...

case when area is null then 'Balto CLMS Take UN' else (area) end as Area,
case when dist is null then 'Balto CLMS Take UN' else (dist) end as dist

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-27 : 11:16:17

area = coalesce(area, 'Balto CLMS Take UN') is a bit simpler (could use isnull but I prefer this).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-27 : 12:20:00

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 12:22:26

quote:
Originally posted by nigelrivett

area = coalesce(area, 'Balto CLMS Take UN') is a bit simpler (could use isnull but I prefer this).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

isnull can be used so long as area field is at least same as length of constant string

see


declare @a varchar(10),@b varchar(18)

select isnull(@a, 'Balto CLMS Take UN') ,coalesce(@a, 'Balto CLMS Take UN'),
isnull(@b, 'Balto CLMS Take UN') ,coalesce(@b, 'Balto CLMS Take UN') 


output
-------------------------------------------------
Balto CLMS	Balto CLMS Take UN	Balto CLMS Take UN	Balto CLMS Take UN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-27 : 12:28:53

Thanks to you both. I like your shorter methods. What is Coalesce doing for this query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 12:29:46

quote:
Originally posted by JJ297

Thanks to you both. I like your shorter methods. What is Coalesce doing for this query?

coalesce looks for first non null value among the list of arguments and returns it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-27 : 13:15:04

Okay great thanks so this is why you are not using is null. I won't use it anymore either. Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 14:33:41

Well..that, and it's ANSI, and it isn't limited to 1 value

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources