Divide by zero error trapping

[Ryan]

I have the following line in a select statement which comes up with a
divide by zero error.

CAST(CASE Splinter_Status
WHEN 'SUR' THEN 0
ELSE CASE WHEN Sacrifice>=1
THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
(m.Gross+m.Sacr ifice)
ELSE 0
END
END AS Float)AS Bond2,

The error happens on the section (m.Gross + m.Sacrifice) as this can
equal zero and throws out the part of the calc that divides by it. It
is correct in some instances that it does so. The full SQL statement
has a large number of these expressions so I need a method I can apply
to any line if possible.

I know that it is mathmatically correct to error where this value is
zero, but what I want to do is set the output of the entire expression
to zero if there is an error.

Realistically an error such as this could happen at a few points in
the expression (or one of many others), so I need to find a way of
catching any error in the expression and setting the return value to
0. I thought of using a CASE statement, but wondered if there was a
better way of looking at this as the case statement would have to
check each variation where it could throw an error.

Any ideas ?

Thanks

Ryan

[Erland Sommarskog]

Ryan (ry********@hot mail.com) writes:

I have the following line in a select statement which comes up with a
divide by zero error.

CAST(CASE Splinter_Status
WHEN 'SUR' THEN 0
ELSE CASE WHEN Sacrifice>=1
THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
(m.Gross+m.Sacr ifice)
ELSE 0
END
END AS Float)AS Bond2,

The error happens on the section (m.Gross + m.Sacrifice) as this can
equal zero and throws out the part of the calc that divides by it. It
is correct in some instances that it does so. The full SQL statement
has a large number of these expressions so I need a method I can apply
to any line if possible.

I know that it is mathmatically correct to error where this value is
zero, but what I want to do is set the output of the entire expression
to zero if there is an error.

SQL Server does happen to other some alternatives in this case, but I would
strongly recomment that you have something like:

ELSE CASE WHEN Sacrifice>=1 AND m.Gross+m.Sacri fice <> 0
THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
(m.Gross+m.Sacr ifice)
ELSE 0
END

The alternatives is to set ANSI_WARNINGS OFF, ARITHABORT OFF and
ARITHIGNORE ON. In this case, SQL Server will silenly set the result to
NULL, which you then would have to apply coalesce to get a 0. But since
these settings are not compatible with indexed views and indexed
computed columns, you can get other problems, and overall it is, in
my opinion, an obscure way of doing things.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gert-Jan Strik]

Probably the easiest way to accomplish what is you is:

CAST(CASE Splinter_Status
WHEN 'SUR' THEN 0
ELSE CASE WHEN Sacrifice>=1
THEN COALESCE(( 3*m.Premium/100-(m.Sacrifice *
3*m.Premium/100)/
NULLIF(m.Gross+ m.Sacrifice,0) ),0)
ELSE 0
END
END AS Float)AS Bond2,

Hope this helps,
Gert-Jan
Ryan wrote:

I have the following line in a select statement which comes up with a
divide by zero error.

CAST(CASE Splinter_Status
WHEN 'SUR' THEN 0
ELSE CASE WHEN Sacrifice>=1
THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
(m.Gross+m.Sacr ifice)
ELSE 0
END
END AS Float)AS Bond2,

The error happens on the section (m.Gross + m.Sacrifice) as this can
equal zero and throws out the part of the calc that divides by it. It
is correct in some instances that it does so. The full SQL statement
has a large number of these expressions so I need a method I can apply
to any line if possible.

I know that it is mathmatically correct to error where this value is
zero, but what I want to do is set the output of the entire expression
to zero if there is an error.

Realistically an error such as this could happen at a few points in
the expression (or one of many others), so I need to find a way of
catching any error in the expression and setting the return value to
0. I thought of using a CASE statement, but wondered if there was a
better way of looking at this as the case statement would have to
check each variation where it could throw an error.

Any ideas ?

Thanks

Ryan

--
(Please reply only to the newsgroup)

[Ryan]

Works perfectly thank you !

R

Gert-Jan Strik <so***@toomuchs pamalready.nl> wrote in message news:<41******* ********@toomuc hspamalready.nl >...

Probably the easiest way to accomplish what is you is:

CAST(CASE Splinter_Status
WHEN 'SUR' THEN 0
ELSE CASE WHEN Sacrifice>=1
THEN COALESCE(( 3*m.Premium/100-(m.Sacrifice *
3*m.Premium/100)/
NULLIF(m.Gross+ m.Sacrifice,0) ),0)
ELSE 0
END
END AS Float)AS Bond2,

Hope this helps,
Gert-Jan
Ryan wrote:

I have the following line in a select statement which comes up with a
divide by zero error.

CAST(CASE Splinter_Status
WHEN 'SUR' THEN 0
ELSE CASE WHEN Sacrifice>=1
THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
(m.Gross+m.Sacr ifice)
ELSE 0
END
END AS Float)AS Bond2,

The error happens on the section (m.Gross + m.Sacrifice) as this can
equal zero and throws out the part of the calc that divides by it. It
is correct in some instances that it does so. The full SQL statement
has a large number of these expressions so I need a method I can apply
to any line if possible.

I know that it is mathmatically correct to error where this value is
zero, but what I want to do is set the output of the entire expression
to zero if there is an error.

Realistically an error such as this could happen at a few points in
the expression (or one of many others), so I need to find a way of
catching any error in the expression and setting the return value to
0. I thought of using a CASE statement, but wondered if there was a
better way of looking at this as the case statement would have to
check each variation where it could throw an error.

Any ideas ?

Thanks

Ryan