Click here to Skip to main content
15,889,281 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear Expert,

How do you handle division by Zero should it occur so that it doesn't create a problem with
the Select statement

as depicted below

((Q1Y2C - Q1Y1C)*100)/Q1Y1C DIFFCQ1P ,
((Q2Y2C - Q2Y1C)*100)/Q2Y1C DIFFCQ2P ,
((Q3Y2C - Q3Y1C)*100)/Q3Y1C DIFFCQ3P ,
((Q4Y2C - Q4Y1C)*100)/Q4Y1C DIFFCQ4P


These are
Q1Y1C ,
Q2Y1C ,
Q3Y1C ,
Q4Y1C



SELECT BRNNAME, SCORE ,
Q1Y1C , Q1Y2C ,
Q2Y1C , Q2Y2C ,
Q3Y1C , Q3Y2C ,
Q4Y1C , Q4Y2C ,
Q1Y1P , Q1Y2P ,
Q2Y1P , Q2Y2P ,
Q3Y1P , Q3Y2P ,
Q4Y1P , Q4Y2P ,
(Q1Y2C - Q1Y1C ) DIFFCQ1C ,
(Q2Y2C - Q2Y1C ) DIFFCQ2C ,
(Q3Y2C - Q3Y1C ) DIFFCQ3C ,
(Q4Y2C - Q4Y1C ) DIFFCQ4C ,
((Q1Y2C - Q1Y1C)*100)/Q1Y1C DIFFCQ1P ,
((Q2Y2C - Q2Y1C)*100)/Q2Y1C DIFFCQ2P ,
((Q3Y2C - Q3Y1C)*100)/Q3Y1C DIFFCQ3P,
((Q4Y2C - Q4Y1C)*100)/Q4Y1C DIFFCQ4P
FROM YFINAL ORDER BY BRNNAME


Division by Zero

((Q1Y2C - Q1Y1C)*100)/Q1Y1C DIFFCQ1P ,
((Q2Y2C - Q2Y1C)*100)/Q2Y1C DIFFCQ2P ,
((Q3Y2C - Q3Y1C)*100)/Q3Y1C DIFFCQ3P,
((Q4Y2C - Q4Y1C)*100)/Q4Y1C DIFFCQ4P

What I have tried:

On going sql server developmental challenges
Posted
Updated 13-Feb-18 4:30am

To be honest, I wouldn't perform the calculation at the server side in a situation like this - unless you were doing this as a set statement that affected something like an INSERT or UPDATE, this is something that should be performed on the client side as this looks suspiciously like something that a business rule should define what you want to do with. For instance, you might want the client side to default divide by zero fields to 0 or you might update a business entity to say that information was missing.
 
Share this answer
 
Comments
Maciej Los 12-Feb-18 16:21pm    
5ed!
In addition to solution #1 by Pete O'Hanlon[^] (completely agree), you can do that on server side, but you have to check if divisor is bigger than zero. For example:
WHERE COALESCE(Q1Y1C,0)>0 AND COALESCE(Q2Y1C,0)>0 AND COALESCE(Q3Y1C,0)>0 AND COALESCE(Q4Y1C, 0)>0 
 
Share this answer
 
The simplest option is to make the divisor Null instead of 0. That way, the division will return Null rather than throwing an error.
((Q1Y2C - Q1Y1C) * 100) / NullIf(Q1Y1C, 0) As DIFFCQ1P
 
Share this answer
 
Comments
Maciej Los 13-Feb-18 11:18am    
Good one!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900