Divide By Zero in the Where Condition

Hi All,
In my SQL I have Where Condition is as follows
Select * From Table1...
WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
And (100 - (Price/MarketValue *100)) > abs(10))
But some of my MarketValue in the table having zero values, I am getting
following error message.
"Divide by zero error encountered."
How to handle this situation. Please help me...
Thanks,
Kannan
kannanBelow are two options you can use to work around this:
CREATE TABLE Table1
(
StatusDate SMALLDATETIME,
Price NUMERIC(7, 2),
MarketValue NUMERIC(7, 2)
)
INSERT Table1 SELECT '1 Feb 2005', 30.00, 25.00
INSERT Table1 SELECT '2 Feb 2005', 50.00, 0.00
INSERT Table1 SELECT '3 Feb 2005', 10.00, 15.00
INSERT Table1 SELECT '4 Feb 2005', 20.00, 300
You can ignore the arithmetic error and turn the error message off by
setting the following setting the follow options
ie.
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET ANSI_WARNINGS OFF
GO
SELECT *
FROM Table1
WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
AND 100 - (Price/MarketValue * 100) > ABS(10)
Or you can exclude all results where the Market Value is 0
ie.
SELECT *
FROM Table1
WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
AND MarketValue <> 0
AND 100 - (Price/MarketValue * 100) > ABS(10)
- Peter Ward
WARDY IT Solutions
"kannan" wrote:

> Hi All,
> In my SQL I have Where Condition is as follows
> Select * From Table1...
> WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
> And (100 - (Price/MarketValue *100)) > abs(10))
> But some of my MarketValue in the table having zero values, I am getting
> following error message.
> "Divide by zero error encountered."
> How to handle this situation. Please help me...
> Thanks,
> Kannan
>
> --
> kannan|||Try using function "nullif".
...
And (100 - (Price / nullif(MarketValue, 0) *100)) > abs(10))
AMB
"kannan" wrote:

> Hi All,
> In my SQL I have Where Condition is as follows
> Select * From Table1...
> WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
> And (100 - (Price/MarketValue *100)) > abs(10))
> But some of my MarketValue in the table having zero values, I am getting
> following error message.
> "Divide by zero error encountered."
> How to handle this situation. Please help me...
> Thanks,
> Kannan
>
> --
> kannan|||Since the SQL Server query optimizer is free to evaluate
things in whatever order is most efficient, adding AND MarketValue <> 0
is not guaranteed to eliminate division by zero.
Alejandro's solution with NULLIF will work, as will the
equivalent use of a CASE expression:
...
Price/CASE WHEN MarketValue = 0 THEN 1 ELSE MarketValue END
...
Depending on what result is needed for rows where MarketValue
equals zero, NULL or another constant value may be a more
appropriate choice than 1 as the alternative of the CASE expression.
Steve Kass
Drew University
P. Ward wrote:
>Below are two options you can use to work around this:
>CREATE TABLE Table1
> (
> StatusDate SMALLDATETIME,
> Price NUMERIC(7, 2),
> MarketValue NUMERIC(7, 2)
> )
>INSERT Table1 SELECT '1 Feb 2005', 30.00, 25.00
>INSERT Table1 SELECT '2 Feb 2005', 50.00, 0.00
>INSERT Table1 SELECT '3 Feb 2005', 10.00, 15.00
>INSERT Table1 SELECT '4 Feb 2005', 20.00, 300
>
>You can ignore the arithmetic error and turn the error message off by
>setting the following setting the follow options
>ie.
>SET ARITHABORT OFF
>SET ARITHIGNORE ON
>SET ANSI_WARNINGS OFF
>GO
>SELECT *
>FROM Table1
>WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
>AND 100 - (Price/MarketValue * 100) > ABS(10)
>
>Or you can exclude all results where the Market Value is 0
>ie.
>SELECT *
>FROM Table1
>WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
>AND MarketValue <> 0
>AND 100 - (Price/MarketValue * 100) > ABS(10)
>
>- Peter Ward
>WARDY IT Solutions
>
>"kannan" wrote:
>
>|||Do you need the records with 0 market value in the resultset? I am assuming
you don't since it wont return true to your formula if it's 0, simply exclud
e
them by adding 'and Market > 0'.
Regards,
Willson
"kannan" wrote:

> Hi All,
> In my SQL I have Where Condition is as follows
> Select * From Table1...
> WHERE StatusDate BETWEEN '01-jan-2005' and '01-Mar-2005'
> And (100 - (Price/MarketValue *100)) > abs(10))
> But some of my MarketValue in the table having zero values, I am getting
> following error message.
> "Divide by zero error encountered."
> How to handle this situation. Please help me...
> Thanks,
> Kannan
>
> --
> kannan