| Author |
Topic |
|
jmersing
Yak Posting Veteran
57 Posts |
 Posted - 2010-10-06 : 14:29:24
|
| Not sure what the best method would be to prevent a div by zero error message I'm getting, this is the part of the sql that is casuing it:Sum(FORECASTTIMESERIES.AHT1*FORECASTTIMESERIES.CALLVOLUME1)+ (FORECASTTIMESERIES.AHT2*FORECASTTIMESERIES.CALLVOLUME2)+ (FORECASTTIMESERIES.AHT3*FORECASTTIMESERIES.CALLVOLUME3)+ (FORECASTTIMESERIES.AHT4*FORECASTTIMESERIES.CALLVOLUME4)+ (FORECASTTIMESERIES.AHT5*FORECASTTIMESERIES.CALLVOLUME5)+ (FORECASTTIMESERIES.AHT6*FORECASTTIMESERIES.CALLVOLUME6)+ (FORECASTTIMESERIES.AHT7*FORECASTTIMESERIES.CALLVOLUME7)+ (FORECASTTIMESERIES.AHT8*FORECASTTIMESERIES.CALLVOLUME8)+ (FORECASTTIMESERIES.AHT9*FORECASTTIMESERIES.CALLVOLUME9)+ (FORECASTTIMESERIES.AHT10*FORECASTTIMESERIES.CALLVOLUME10)+ (FORECASTTIMESERIES.AHT11*FORECASTTIMESERIES.CALLVOLUME11)+ (FORECASTTIMESERIES.AHT12*FORECASTTIMESERIES.CALLVOLUME12)+ (FORECASTTIMESERIES.AHT13*FORECASTTIMESERIES.CALLVOLUME13)+ (FORECASTTIMESERIES.AHT14*FORECASTTIMESERIES.CALLVOLUME14)+ (FORECASTTIMESERIES.AHT15*FORECASTTIMESERIES.CALLVOLUME15)+ (FORECASTTIMESERIES.AHT16*FORECASTTIMESERIES.CALLVOLUME16)+ (FORECASTTIMESERIES.AHT17*FORECASTTIMESERIES.CALLVOLUME17)+ (FORECASTTIMESERIES.AHT18*FORECASTTIMESERIES.CALLVOLUME18)+ (FORECASTTIMESERIES.AHT19*FORECASTTIMESERIES.CALLVOLUME19)+ (FORECASTTIMESERIES.AHT20*FORECASTTIMESERIES.CALLVOLUME20)/sum(FORECASTTIMESERIES.CALLVOLUME1)+ (FORECASTTIMESERIES.CALLVOLUME2)+ (FORECASTTIMESERIES.CALLVOLUME3)+ (FORECASTTIMESERIES.CALLVOLUME4)+ (FORECASTTIMESERIES.CALLVOLUME5)+ (FORECASTTIMESERIES.CALLVOLUME6)+ (FORECASTTIMESERIES.CALLVOLUME7)+ (FORECASTTIMESERIES.CALLVOLUME8)+ (FORECASTTIMESERIES.CALLVOLUME9)+ (FORECASTTIMESERIES.CALLVOLUME10)+ (FORECASTTIMESERIES.CALLVOLUME11)+ (FORECASTTIMESERIES.CALLVOLUME12)+ (FORECASTTIMESERIES.CALLVOLUME13)+ (FORECASTTIMESERIES.CALLVOLUME14)+ (FORECASTTIMESERIES.CALLVOLUME15)+ (FORECASTTIMESERIES.CALLVOLUME16)+ (FORECASTTIMESERIES.CALLVOLUME17)+ (FORECASTTIMESERIES.CALLVOLUME18)+ (FORECASTTIMESERIES.CALLVOLUME19)+ (FORECASTTIMESERIES.CALLVOLUME20) As AHT2 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jmersing
Yak Posting Veteran
57 Posts |
Posted - 2010-10-06 : 15:14:19
|
| NOt exactly sure how to do that. I would typically run this in MS Access and use iiF. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-10-06 : 15:18:46
|
| CASE is a good method although you will have to repeat your denominator expression which will really make Tara's eyes hurt. An alternative is to wrap the denominator expression in a NULLIF function: nullif(<expr>, 0)That will result in a NULL value rather than an error. If you prefer a "0" to NULL then you could nest the entire expression in an ISNULL. ie: ISNULL( <exp1> / NULLIF,<expr>,0)), 0)CASE would be like:case when <expr2> = 0 then 0 else <expr1>/<expr2> endBe One with the OptimizerTG |
|
|
|
jmersing
Yak Posting Veteran
57 Posts |
Posted - 2010-10-06 : 15:40:04
|
| A zero would work so I wanted to try the ISNULL function but i cant seem to get the syntaxsimplified version i was trying:ISNULL(Sum((FORECASTTIMESERIES.AHT1*FORECASTTIMESERIES.CALLVOLUME1)+ (FORECASTTIMESERIES.AHT2*FORECASTTIMESERIES.CALLVOLUME2))/Sum((FORECASTTIMESERIES.CALLVOLUME1)+ (FORECASTTIMESERIES.CALLVOLUME2)),0)but it still tells me I have a divide by zero error |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-10-06 : 15:47:48
|
| you missed the NULLIF around the denominator.ISNULL( <numeratorExpr> / NULLIF(<denominatorExpr>,0), 0)Be One with the OptimizerTG |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jmersing
Yak Posting Veteran
57 Posts |
Posted - 2010-10-06 : 16:04:15
|
| Now I understand - it works perfectly - Thank You! |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-10-06 : 16:11:08
|
| You're welcome!You should take Brett's jibe to heart and think about normalizing your table. It's not a good design to have 20 flavors of each column.Be One with the OptimizerTG |
|
|
|
|
Division by zero error?