Case When 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 2000 Forums

Transact-SQL (2000)

Case When Is Null

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-28 : 16:47:46

I'm trying to get a return of 0 if a record is not found
in a certain table.

SELECT a.ID,
a.assignedID,
b.Manufacturer,
(CASE h.id WHEN (is null) THEN 0 ELSE h.id END) as calId
--h.id as calId

FROM tblEquipments a,
tblManufacturers b,
tblCalibrationReview h

WHERE a.manufacturer = b.manufacturerID
AND h.equipmentId = a.ID

Some records are not in tblCalibrationReview and if not, return 0
for h.id

Suggestions?

Thanks,

Zath

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-28 : 17:01:31

You need to use an outer join otherwise the rows where there is no tblCalibrationReview are not returned. Here it is, you should use ANSI join syntax like this too.

SELECT a.ID,
a.assignedID, 
b.Manufacturer, 
coalesce(h.id, 0) as calId 

FROM tblEquipments a 
INNER JOIN tblManufacturers b ON a.manufacturer = b.manufacturerID 
LEFT OUTER JOIN tblCalibrationReview h ON h.equipmentId = a.ID

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-28 : 17:20:26

another way is:

...
ISNULL(H.Id, 0) As CalId,
...

************************
Life is short. Enjoy it.
************************

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-28 : 17:29:59

Most people around here prefer COALESCE because it is ANSI standard (ISNULL is SQL Server only), because it can accept any number of arguments, and it doesn't exhibit the ISNULL quirkiness discussed here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81124

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-03-28 : 18:55:52

Allright..my BOL updated as well..I kinda knew there was a subtle difference between COALESCE and ISNULL but I've never seen the issue myself. Now that I do, COALESCE rules..

************************
Life is short. Enjoy it.
************************

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-29 : 08:34:47

Thanks everyone!

Got it working just fine now.

And good read about COALESCE.

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources