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 |
|
Ats
Starting Member
32 Posts |
 Posted - 2009-02-07 : 10:50:14
|
| I have created the following query but if the cer allowance is null or 0 get an error as it can not divide by 0 is there any way I can stop this errorSELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1 FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier WHERE compliance.Company_ID = 890 and compliance.year1 = '2008' GROUP BY compliance.Company_ID, compliance.year1 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-07 : 10:56:04
|
| but you're not dividing by cer.cerallowance (at least i cant see it in posted query) |
 |
|
|
Ats
Starting Member
32 Posts |
Posted - 2009-02-07 : 10:57:24
|
| The cerallowance gets divided by 100. so if the cerallowance is 0 the sum is 0/100 which causes the error |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-07 : 11:49:30
|
| 0/100 will give you 0. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-07 : 11:50:07
|
| it wont cause divide be 0 error, unless you use this results somewhere above this in denominator. anyways in that case just use NULLIF(cer,0) to avoid divide by 0 error |
|
|
|
Ats
Starting Member
32 Posts |
Posted - 2009-02-07 : 11:54:18
|
| Ok Thanks for your help |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-07 : 12:54:26
|
| welcome |
|
|
|
|
|
|
Divide by 0 error