SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Divide by zero in the subscriber and NOT in the publisher


Divide by zero in the subscriber and NOT in the publisher

Author
Message
clearbystander
clearbystander
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 20
Hi

I run the same query against two DB, one is the publisher and other is the subscriber.
The two DB should be identical but the subscriber raise the "Divide By Zero" error while the publisher return the correct result.[b][/b]
Of course.... NONE OF THE RETURNED ROWS HAVE A ZERO in the relevant fields!!!
w00t
This is the query

SELECT
CASE
WHEN
BAL_IN.FK_CURR = 'EUR' then 1.0 -- If the currency is EURO the exchange rate is one
ELSE -- Otherwise calculate it.
BAL_IN.LEDGER_BAL_ORG_AMT/ BAL_IN.AC_FUNC_AMT -- HERE RAISE THE DIVIDE BY ZERO ERROR
END AS Exchange_Rate ,

CASE
WHEN
BAL_IN.FK_CURR = 'EUR' THEN DET_IN.DTL_AMT
ELSE
DET_IN.DTL_AMT/(BAL_IN.LEDGER_BAL_ORG_AMT/BAL_IN.AC_FUNC_AMT) -- This statement is similar but DO NOT Raise the Error(!!?)
END AS Exchanged_amount,

BAL_IN.AC_FUNC_AMT ,
BAL_IN.LEDGER_BAL_ORG_AMT,
DET_IN.DTL_AMT,

FROM
SICSNTPC.AC_LEDGER_DETAIL AS DET_IN ,
SICSNTPC.AC_LEDGER_BALANCE AS BAL_IN
WHERE
Where conditions......

I've found a kind of bypass using this statement
WHEN (ABS(BAL_IN.AC_FUNC_AMT)) <> 0 then ABS(BAL_IN.LEDGER_BAL_ORG_AMT)/ ABS(BAL_IN.AC_FUNC_AMT)

The problem has appeared lately but I can't imagine the origin of that.
Someone has ideas of what could be the reason of this disconcerting behaviour ?

Many thanks for the help
:-)

Giuliano
Steve Thompson
Steve Thompson
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 2102
I don't actually have a solid answer for why the values in rows are different between publisher and subscriber, but I have a couple of suggestions for troubleshooting further:

I've found a kind of bypass using this statement
WHEN (ABS(BAL_IN.AC_FUNC_AMT)) <> 0 then ABS(BAL_IN.LEDGER_BAL_ORG_AMT)/ ABS(BAL_IN.AC_FUNC_AMT)

My first thought is that your bypass solution could actually be considered a best practice; i.e. whenever doing division in the data layer I would wrap that in a CASE to ensure that a Divide By Zero doesn't occur in run time.

SELECT
CASE
WHEN
BAL_IN.FK_CURR = 'EUR' then 1.0 -- If the currency is EURO the exchange rate is one
ELSE -- Otherwise calculate it.
BAL_IN.LEDGER_BAL_ORG_AMT/ BAL_IN.AC_FUNC_AMT -- HERE RAISE THE DIVIDE BY ZERO ERROR
END AS Exchange_Rate ,

CASE
WHEN
BAL_IN.FK_CURR = 'EUR' THEN DET_IN.DTL_AMT
ELSE
DET_IN.DTL_AMT/(BAL_IN.LEDGER_BAL_ORG_AMT/BAL_IN.AC_FUNC_AMT) -- This statement is similar but DO NOT Raise the Error(!!?)
END AS Exchanged_amount,

As to why you get a Divide By Error in the earlier column but not the latter, I'm guessing that that's b/c the engine will only throw the first exception; once it encounters one Divide By Zero it throws an exception and stops processing - so it doesn't evaluate the second expression.

As to why the values are different (assuming that there is some difference on the subscriber side), it's possible for rows to get out of sync. In 2008+ there's a utility that can help identify this issue called tablediff but I don't think it's available for 2005: http://technet.microsoft.com/en-us/library/ms162843.aspx. But it shouldn't be hard to do a column by column comparison of the two rows to see what values are different. You don't mention what type of replication you're using. I use transactional w/ updateable subscriptions and this model adds a column called msrepl_tran_version (maybe this gets added in all models?). If the values for this column are different then the rows are not considered in sync and changes to one may not replicate to the other. I've been able to fix this by updating one side's value to match the other's, but this is usually a sign that you have deeper issues that are not being addressed.

Not sure if this helps, but good luck with your troubleshooting.
clearbystander
clearbystander
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 20
Hi Steve

thanks for the kind answer, sometimes just exchanging ideas helps to clarify themselves.
:-)
And then..... we have some more info

My first thought is that your bypass solution could actually be considered a best practice;
i.e. whenever doing division in the data layer I would wrap that in a CASE to ensure that a
Divide By Zero doesn't occur in run time.


Yep this was also my first thought but then in the situation where the denominator were really
zero I had to handle the Exchange_Rate returning null because none of the WHEN/ELSE where
satisfied.....
but again I'was puzzled because none of the fields returned by the query were zero.


As to why you get a Divide By Error in the earlier column but not the latter, I'm guessing
that that's b/c the engine will only throw the first exception; once it encounters one Divide
By Zero it throws an exception and stops processing - so it doesn't evaluate the second
expression.


I tried to reverse the CASE and the error moved to the second (formerly first) CASE!

I posted the same append in the section Programming of this forum and john.rees-894283 replied this:

http://www.sqlservercentral.com/Forums/Topic1489248-338-1.aspx

A possible reason for this error is that the division expressions are being evaluated for some
rows before those rows are excluded by the WHERE clause.
The same problem is often observed
when CASTing datatypes, and using a WHERE clause to filter only those rows where the CAST would succeed.
This can happen because the optimiser is free to evaluate your SELECT expressions either
before or after filtering by the WHERE clause. Even if the data in your two databases is the
same, other factors (indexing, server resources...) may affect the optimser.


Then I counted the rows in the table where this field/denominator was zero and, surprise, there are some rows where IT IS zero.
It should not be zero but it is....

So at the end.... May be I'll have to leave a piece of wrong code to remedy a piece of bad data !!Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search