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.
Author |
Topic |
aswindba1
Yak Posting Veteran
62 Posts |
Posted - 2013-01-23 : 16:55:47
|
Can Any one help me with belwo query.UPDATE T1SET T1.[Count] = 1.00/(SELECT COUNT(*) FROM [Tracker] T2 WHERE T1.[Application ID] = T2.[Application ID])FROM [Tracker] T1 Actually in [Application ID] we ave some null values and getting the follwing error. Could you Please modify the query.Msg 8134, Level 16, State 1, Line 1Divide by zero error encountered.The statement has been terminated. |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-23 : 17:37:05
|
UPDATE T1SET T1.[Count] = 1.00/ NULLIF ((SELECT COUNT(*) FROM [Tracker] T2 WHERE T1.[Application ID] = T2.[Application ID]),0)FROM [Tracker] T1JimEveryday I learn something that somebody else already knew |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-01-23 : 17:40:55
|
quote: Originally posted by jimf UPDATE T1SET T1.[Count] = 1.00/ NULLIF ((SELECT COUNT(*) FROM [Tracker] T2 WHERE T1.[Application ID] = T2.[Application ID]),0)FROM [Tracker] T1JimEveryday I learn something that somebody else already knew
Of course Jim meant:UPDATE T1SET T1.[Count] = 1.00/ NULLIF ((SELECT COUNT(*) FROM [Tracker] T2 WHERE T1.[Application ID] = T2.[Application ID]),1)FROM [Tracker] T1 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-23 : 17:46:30
|
No I didn't. NULLIF says if the 1st value = the 2nd value, return NULL. It's different than an isnull statement. Mine says "if the divisor = 0, return null instead"JimEveryday I learn something that somebody else already knew |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-01-23 : 18:06:30
|
quote: Originally posted by jimf No I didn't. NULLIF says if the 1st value = the 2nd value, return NULL. It's different than an isnull statement. Mine says "if the divisor = 0, return null instead"JimEveryday I learn something that somebody else already knew
My mistake - sorry. |
|
|
|
|
|