Divide by zero error trapping

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.Sacrifice)
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

RyanRyan (ryanofford@.hotmail.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.Sacrifice)
> 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.Sacrifice <> 0
THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/
(m.Gross+m.Sacrifice)
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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.Sacrifice)
> 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)|||Works perfectly thank you !

R

Gert-Jan Strik <sorry@.toomuchspamalready.nl> wrote in message news:<41251949.4D0CF7B3@.toomuchspamalready.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.Sacrifice)
> > 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