question about nulls and coalesce

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)

question about nulls and coalesce

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-04-14 : 11:38:30

Morning,

I have a list of values from one field "ProgramName"

I tried using coalesce but I dont think its going to work. I tried this:


select coalesce(programname,programname) as test
from POWithWorkCenters po
where po.Customer_PO = '102360' 
order by po.Job

and got the following results:


test
---------------
NULL
Value3
Value3
Value3
Value3
Value3
DADE - Value3
DADE - Value3
DADE - Value3
DADE - Value3
NULL
(11 row(s) affected)

Is there a way to squish (technical term) the values into one and say show me the not null value? That's why I thought coalesce might handle this.

Any ideas would be appreciated.

Thanks

Laura

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-14 : 11:51:37

coalesce returns the first non-null value from the supplied list. What is it you're trying to do?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-04-14 : 13:33:51

Just that. Is there a reason why it would not work as advertised?

Kristen
Test

22859 Posts

Posted - 2010-04-14 : 13:35:59

"coalesce(programname,programname)"

You've got exactly the same parameter name for both the parameters. You need different names for each parameter for COALESCE to be any use.

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-04-14 : 13:51:28

Rats, thats the only field that holds that information. Oh well.

thanks anyway.

Laura

Kristen
Test

22859 Posts

Posted - 2010-04-14 : 14:46:27

Sounds like you are not understanding how COALESCE is supposed to work ??

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-04-14 : 15:07:30

So it would seem. Thanks anyway.

Kristen
Test

22859 Posts

Posted - 2010-04-15 : 04:03:44

COALESCE picks the first non-null parameter - hence having the same parameter twice doesn't accomplish anything (if it is NULL the "last" parameter is taken which, being the same, will also be NULL)

Often COALESCE will be used to force a value, such as:


COALESCE(TheColumnThatMayBeNull, 'MyDefaultValue')

other times just to pick the first non-null value from a list.

If you were hoping for a different outcome if you explain it then we can try to make suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources