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: