March 14, 2012 at 3:30 pm
This query was working on SQL 2000 but on SQL 2008 is giving me the error "Divide by zero error encountered.":
UPDATE T_PRICE
SET VAR =
CASE
WHEN PRICE <> 0 AND COST <> 0 THEN ROUND((((COST-PRICE)/COST)*100),0)
ELSE 999
END
FROM
T_PRICE
WHERE
((PRICE <> 0 AND COST <> 0) AND (VAR <> ROUND((((COST-PRICE)/COST)*100),0))) OR
((PRICE = 0 OR COST = 0) AND VAR <> 999) OR
(VAR IS NULL)
Can anybody help, with this where clause, but logic has to stay the same?
March 14, 2012 at 3:34 pm
Please read the first article I reference below in my signature block below regarding asking for help. Follow the instructions on what to post and how. In this case, make sure your sample data has data that works using SQL Server 2000 and fails using SQL Server 2008.
March 15, 2012 at 12:06 am
My guess is that failure is occurring at evaluation of
(VAR <> ROUND((((COST-PRICE)/COST)*100),0))) O
In the WHERE clause.
But its just a guess without sample data.
PS.
That evaluation seems a little redundant?
March 15, 2012 at 12:20 am
I believe the answer is simple. You have two different servers. Are you absolutely sure the data is on the new server doesn't have a "0" in it somewhere for the Cost column? Try the following and find out.
SELECT COUNT(*)
FROM your2k8table
WHERE Cost = 0
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2012 at 12:36 am
March 15, 2012 at 4:31 am
logavina (3/14/2012)
This query was working on SQL 2000 but on SQL 2008 is giving me the error "Divide by zero error encountered.":UPDATE T_PRICE
SET VAR =
CASE
WHEN PRICE <> 0 AND COST<> 0 THEN ROUND((((COST-PRICE)/COST)*100),0)
ELSE 999
END
FROM
T_PRICE
WHERE
((PRICE <> 0 AND COST <> 0) AND (VAR <> ROUND((((COST-PRICE)/COST)*100),0))) OR
((PRICE = 0 OR COST = 0) AND VAR <> 999) OR
(VAR IS NULL)
Can anybody help, with this where clause, but logic has to stay the same?
Back few years ago, I was working on the project which was one of the first in the world to use SQL2005 (we worked almost as MS free testing site :-)). The nature of the project was an ETL of very large datasets. And we have found that WHERE conditions sometimes didn't apply as per SQL2K. For example, query like that:
SELECT Col1/Col2 FROM Table1 WHERE Col2 != 0
or even that:
SELECT CASE WHEN Col2 != 0 THEN Col1/Col2
ELSE NULL --or 0 whatever you want
END
FROM Table1 WHERE Col2 != 0
Both produced the exact same error: Division by zero.
It did happen occasionally on quite large datasets (and mainly when there were some JOINs involved). Also, some checks for datatype convertibility of didn't work also...
The call was made to MS and MS confirmed that with a new optimiser it's possible that validation of result may happen before applying filters...
And advised to get around this problem by inserting filtered out records into intermediate tables:
SELECT Col1, Col2 INTO #Table2 FROM Table1 WHERE Col2 != 0
SELECT Col1/Col2 FROM #Table1
I've suggested a bit simpler way: just "convert" invalid values into NULL:
SELECT Col1/NULLIF(Col2,0) FROM Table1 WHERE Col2 != 0
I don't know if MS have resolved this "feature", but it will not surprise me if it's still the case.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply