What seems like a simple [Live]/[Target]*100 calculation is proving to much for me as i'm having to take into account values of '0'. When i use this simple calculation, it returns a 'cannot divide by zero' error. Very frustrating.
Additionall, for sites 3PT and 5KL, i'm actually not sure what conversion % is mathematically and logically correct... My head is frazzled!
In DB2, use the expression
COALESCE( Live * 100. / NULLIF(Target , 0) , 0 )
(I think this is an idiomatic expression to calculate percentage for INTEGERs on DB2.)
Divide by zero is not possible(not defined)
for mathematical "field" algebraic system(i.e. system on which addition/subtraction/multiplication/division were difined).
So, I thought to return NULL or 0 for divided by 0 would be practical.
Return NULL: Live * 100. / NULLIF(Target , 0)
Return 0: COALESCE( Live * 100. / NULLIF(Target , 0) , 0 )
Spot on, that did just the trick! I had a slight issue with my update query, where it looked like it was recording the wrong value. However this was a rounding issue, i've now changed the 'Conversion' field to a decimal, re-ran the update and all is i would expect.
To me, accuracy of data calculation is the most important thing. If a calculation is 5.88%, i need THAT to be displayed rather than 6%.
Therefore, "sum(capacity) * 100. / NULLIF(count(deployments) , 0)" will be in my script.
Thanks for your help!
I will do some research myself, but could you give me your understanding on what "COALESCE" does as a function? I've never come across it before, and after trying lots of different methods to resolve this issue, it was the golden ticket.