Division by zero error
-
Hi All I'm a newbie to SQL coding, and I have a problem which I'm hoping you can help with regarding the Division by zero error. I have a calculated field in a sql statement where the results from 1 case statements is divided by another. Is there a way to catch the division by zero error and insert a '0' without the code throwing an exception? Thank you in advance.
-
Hi All I'm a newbie to SQL coding, and I have a problem which I'm hoping you can help with regarding the Division by zero error. I have a calculated field in a sql statement where the results from 1 case statements is divided by another. Is there a way to catch the division by zero error and insert a '0' without the code throwing an exception? Thank you in advance.
Unsy wrote:
Is there a way to catch the division by zero error and insert a '0' without the code throwing an exception
Most likely, but could you post the statement. Without that it's quite impossible to say how to implement it.
The need to optimize rises from a bad design.My articles[^]
-
Unsy wrote:
Is there a way to catch the division by zero error and insert a '0' without the code throwing an exception
Most likely, but could you post the statement. Without that it's quite impossible to say how to implement it.
The need to optimize rises from a bad design.My articles[^]
Hi Mika I dont have the exact code with me at the moment. It was similar to this...
SELECT
Field1
,Field2
,SUM(
SUM(CASE WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 END))
/
SUM(CASE WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 TotalPaid END))
)
FROM
Table1
GROUP BY
Field1
,Field2The idea is to get a total of how many Term - 20's are over 0 which were representing a loan term. Anything less than 0 would mean it has gone over term and should not be counted. This was to be divided by the sum of total paid to calculate performance. Please excuse the SQL code if it is full of errors as I am 250 miles from home and have no way of testing if it is right. :sigh: Thanks
-
Hi Mika I dont have the exact code with me at the moment. It was similar to this...
SELECT
Field1
,Field2
,SUM(
SUM(CASE WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 END))
/
SUM(CASE WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 TotalPaid END))
)
FROM
Table1
GROUP BY
Field1
,Field2The idea is to get a total of how many Term - 20's are over 0 which were representing a loan term. Anything less than 0 would mean it has gone over term and should not be counted. This was to be divided by the sum of total paid to calculate performance. Please excuse the SQL code if it is full of errors as I am 250 miles from home and have no way of testing if it is right. :sigh: Thanks
Unsy wrote:
Please excuse the SQL code if it is full of errors as I am 250 miles from home
No problem. And the problem was that the divisor is equal to zero in some cases. If it's zero, you wanted the result of the division to be zero. In that case you could for example use inline view for intermediate results and make the statement something like:
SELECT
Field1
,Field2
,SUM( CASE Divisor
WHEN 0 THEN 0
ELSE Amount / Divisor
END)
FROM (
SELECT Field1,
Field2,
SUM(CASE
WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0
THEN 0
ELSE 1
END
) amount,
SUM(CASE
WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0
THEN 0
ELSE 1
END
) divisor
FROM
Table1
GROUP BY
Field1
,Field2) alias
GROUP BY
Field1
,Field2The need to optimize rises from a bad design.My articles[^]
-
Unsy wrote:
Please excuse the SQL code if it is full of errors as I am 250 miles from home
No problem. And the problem was that the divisor is equal to zero in some cases. If it's zero, you wanted the result of the division to be zero. In that case you could for example use inline view for intermediate results and make the statement something like:
SELECT
Field1
,Field2
,SUM( CASE Divisor
WHEN 0 THEN 0
ELSE Amount / Divisor
END)
FROM (
SELECT Field1,
Field2,
SUM(CASE
WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0
THEN 0
ELSE 1
END
) amount,
SUM(CASE
WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0
THEN 0
ELSE 1
END
) divisor
FROM
Table1
GROUP BY
Field1
,Field2) alias
GROUP BY
Field1
,Field2The need to optimize rises from a bad design.My articles[^]