Divide by zero error! Help!

I don't understand why I get the error "Server: Msg 8134, Level 16,
State 1, Line 1
Divide by zero error encountered."
I check for 0, actually if I change the statement after ELSE to 2, it
will run with no issue and get 1 since the when statement is 0 in this
case. Please help.
SELECT
CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
ICFPM_USER_14))= 0 THEN 1
ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
ICFPM_USER_14)))
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'Hi
Don't assume that the execution order of an ELSE will follow the order you
coded. It may evaluate both CASEs in parallel and then use the output later.
During query execution, your query may be run differently to what you think.
This can be influenced by number of processors, RAM available, indexes and
statistics.
You need to make sure that your query does not have the possibility of
failing, no matter the code execution path.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<zod91@.yahoo.com> wrote in message
news:1149560228.320495.202850@.j55g2000cwa.googlegroups.com...
>I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
>
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'
>

Divide by zero error! Help!

I don't understand why I get the error "Server: Msg 8134, Level 16,
State 1, Line 1
Divide by zero error encountered."
I check for 0, actually if I change the statement after ELSE to 2, it
will run with no issue and get 1 since the when statement is 0 in this
case. Please help.
SELECT
CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
ICFPM_USER_14))= 0 THEN 1
ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
ICFPM_USER_14)))
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'Hi
Don't assume that the execution order of an ELSE will follow the order you
coded. It may evaluate both CASEs in parallel and then use the output later.
During query execution, your query may be run differently to what you think.
This can be influenced by number of processors, RAM available, indexes and
statistics.
You need to make sure that your query does not have the possibility of
failing, no matter the code execution path.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<zod91@.yahoo.com> wrote in message
news:1149560228.320495.202850@.j55g2000cwa.googlegroups.com...
>I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
>
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'
>

Divide by zero error! Help!

I don't understand why I get the error "Server: Msg 8134, Level 16,
State 1, Line 1
Divide by zero error encountered."

I check for 0, actually if I change the statement after ELSE to 2, it
will run with no issue and get 1 since the when statement is 0 in this
case. Please help.

SELECT
CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
ICFPM_USER_14))= 0 THEN 1
ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
ICFPM_USER_14)))

END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'zod91@.yahoo.com (zod91@.yahoo.com) writes:
> I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'

I guess that you get the error, because SQL Server computes both SUMs
when traversing the rows. If it were to do the statement literaly,
it would first have to traverse the rows to compute the first sum,
and if that sum is not 0, traverse once more. So you would need:

SELECT CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14)) = 0 THEN 1
ELSE SUM(REV_ORDER_QTY /
CASE WHEN CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14) <> 0
THEN CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14)
ELSE 1 -- or whatever
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'

This still looks funny to me. As soon as any of the columns is 0 for a
row, the outcome is always 1. Then again, I don't anything about the
business, so maybe this is OK.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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

Divide By Zero - How to prevent

Gentle / Ladies
I am converting over a computer system from HQL to SQL and it has some
statements that I need to convert.
If you look at the below line of HQL code you will see the 'greatest'
function which as used prevents the divide by zero error under HQL. It works
on the absolute value of the second parameter so ( 100 - CurrentMargin(value
of 125) ) would equal 25 for the comparison of which is greater 1 or 25 but
does not impact the true value of -25. The main purpose is if the
CurrentMargin were to equal 100, subtracting it from 100 giving a value of 0
thus the greatest function would return a 1.
Select
ItemNum,
(CurrentMargin / greatest(1, (100 - CurrentMargin)) ) * InventoryTurns as
ROII
How can I rewrite it so that it prevents the divide by zero error if the
CurrentMargin is 100.
Sorry if I rambled on a bitMark,
You can use the SQL CASE expression:
select
ItemNum,
(
CurrentMargin
/
CASE WHEN 100-CurrentMargin < 1 THEN 1 ELSE 100 - CurrentMargin END
) * InventoryTurns as ROII
...
Steve Kass
Drew University
Mark Moss wrote:

>Gentle / Ladies
>
>I am converting over a computer system from HQL to SQL and it has some
>statements that I need to convert.
>If you look at the below line of HQL code you will see the 'greatest'
>function which as used prevents the divide by zero error under HQL. It work
s
>on the absolute value of the second parameter so ( 100 - CurrentMargin(valu
e
>of 125) ) would equal 25 for the comparison of which is greater 1 or 25 but
>does not impact the true value of -25. The main purpose is if the
>CurrentMargin were to equal 100, subtracting it from 100 giving a value of
0
>thus the greatest function would return a 1.
>Select
>ItemNum,
>(CurrentMargin / greatest(1, (100 - CurrentMargin)) ) * InventoryTurns as
>ROII
>How can I rewrite it so that it prevents the divide by zero error if the
>CurrentMargin is 100.
>Sorry if I rambled on a bit
>
>|||As I understand, if the CurrentMargin is 99.5, you do not really want
to divide by 1 (you want do divide by 100-99.5). In this case, you
might try:
SELECT ItemNum,
CurrentMargin/NULLIF(100-CurrentMargin,0)*InventoryTurns
...
or:
SELECT ItemNum,
CurrentMargin/ISNULL(NULLIF(100-CurrentMargin,0),1)*InventoryTurns
...
Razvan

Distributed Transaction Persistent Error

When I try to make a distributed transaction, I always get this error:

Server: Msg 7391, Level 16, State 1, Line 14
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

That is although, I am sure that both servers have active DTC.

Have you verified from Control Panel->Component Services->Computers->My Computer->Properties->Security Configuration that Network DTC access is allowed and other settings on the same tab?

|||

No, I didn't verify that.

Now I checked the Network DTC Access check box in the place you mentioned, but still the same error persistent.

What are the other settings in the same tab that I should check?

Thanks

|||

You may want to check the following to trouble shoot the Distributed transaction errors:

1. Verify there are no MS DTC firewall issues. Please see link: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306843 on how to trouble shoot them.
2. In case the two computers are running in domains or workgroups that do not trust each other, the MSDTC may fail to mutually authenticate. Please see link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/dc45d41e-58f7-412f-834d-fec72e00fc01.asp for more details.

Thanks

Suroor

|||

The matter is very sophisticated.

Isn't there an easier way than that whole sophistication?

What are the settings to allow under MSDTC "Allow Network Access"?

Thanks

|||

http://support.microsoft.com/kb/827805#kb3

http://support.microsoft.com/kb/329332/

HTH

distributed transaction failure

Im attempting to insert the result set from a remote query into a local table and Im getting the following error:

Msg 8501, Level 16, State 1, Line 1
MSDTC on server 'REMOTESVR' is unavailable.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
Msg 7391, Level 16, State 1, Line 1

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

MSDTC is running on both servers. Has anyone ever seen this or have any insight into the cause?


The code that Im trying to run is:

create table msver
(
[Index] int,
[Name] varchar(30),
Internal_Value varchar(20),
Character_Value varchar(512)
)

insert into msver
exec ('exec [REMOTESVR].master.dbo.xp_msver')

Note that the remote query works fine.

Thanks!
EricMake sure MSDTC is started on both the servers.|||I might be wrong, but I don't think you're going to get xp_msver to work this way. Run it as:

insert msver
exec [REMOTESVR].sp_executesql 'exec master.dbo.xp_msver'

See if that works. I don't have my laptop hooked up, or I would test it really quick at the lab. :)|||Check on the 'remoteserver' SQL server error log for #
"... server Attempting to initialize Distributed Transaction Coordinator..."