Division query

i have an equation which i have to implement in query:
availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
when tried the query i get avail as zero always, help me outNAVIN.D skrev:

> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out
Without knowing what the above equation is or where it is used the easy
explanation would be that it's caused by integer division, ie.
everything below 1.0 will become 0.
/impslayer, aka Birger Johansson|||You will get an integer result when all operands are integer. You can
specify a decimal operand if you need decimal result. The result will
decimal in that case because the decimal data type has a higher precedence
than integer. Try:
availa = (1.0-[return0]/sum(r1+r2+r3+r4+r5))
Hope this helps.
Dan Guzman
SQL Server MVP
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:48C5F07C-1023-4AB1-9B17-356321EB95CD@.microsoft.com...
> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out|||If SUM(r1+r2+r3+r4+r5) > (1-[return0]), you will get zero because SQL will
return a value within the datatype integer. (The answer will be truncated).
Try
(1.00 - [return0]/sum(r1+r2+r3+r4+r5)
"NAVIN.D" wrote:

> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out|||thank you mark and imsplayer it was decimal and integer funda only i got it
immd after posting anyways thanks for the reply
"Mark Williams" wrote:
> If SUM(r1+r2+r3+r4+r5) > (1-[return0]), you will get zero because SQL will
> return a value within the datatype integer. (The answer will be truncated)
.
> Try
> (1.00 - [return0]/sum(r1+r2+r3+r4+r5)
>
> --
>
> "NAVIN.D" wrote:
>

Division by zero problem

I've got the following SQL

SELECT count(*) FROM tablea A
JOIN tableb B ON ..etc..
WHERE a.string_val = 'test' AND
b.divider 0 AND
a.number 0 AND
(a.number / b.divider 0)

The b.divider value can be 0, but still I get the division by zero
error message.
I guess the reason is that the database performs the where statements
one at the time?

So when performing the division a.number / b.divider it could get 0 in
the divider even if b.divider 0 is also part of the where
statement??

My question is then.. How can I work around this problem? Changing the
database to set b.divider always 0 is not an optionMaybe this?

SELECT count(*) FROM tablea A
JOIN tableb B ON ..etc..
WHERE a.string_val = 'test' AND
b.divider 0 AND
a.number 0 AND
(a.number / NULLIF(b.divider,0) 0)|||Thank you :)

Division by Zero Error

I am getting a division by zero error from the SQL below, I believe I need to utilize Cast because it could be a negative number and I need to be able to view 8 positions to the right of the dec. (0000.00000000). Any help would be greatly appreciated.

SELECT FromDate AS MonthOneDate,State,Description,SUM(Flowthru) AS CELCSumFT,SUM(TotalCount) AS CELCsumCNT,
(SUM(Flowthru)/SUM(TotalCount)) * 100 AS CELCFtPct,
RetailNum AS ARZSumFT,RetailDen AS
ARZSumCnt,(RetailNum/RetailDen) * 100 AS ARZFtPct,
((RetailNum/RetailDen) - SUM(Flowthru) /SUM(TotalCount)) / SQRT(((SUM(Flowthru) + RetailNum) / (SUM(TotalCount) +RetailDen)) * (1-(SUM(Flowthru)+RetailNum)/(SUM(TotalCount) + RetailDen)) * (1 / SUM(TotalCount)+1/RetailDen)) AS ZScoreMonth1
FROM pmMidwest33_BlueRed WHERE TotalCount <> 0 AND CLEC <>
'' AND Acna NOT IN ('TRE','TRD') AND STATE IN ('IL','IZ','TR','MI','RE')
AND MONTH(FromDate) = MONTH(DATEADD(m,-2,GETDATE()))
AND YEAR(FROMDATE) = YEAR(DATEADD(m,-2,GETDATE()))
GROUP BY FromDate,State,Description,RetailNum,RetailDenDid you figure what part of your SQL query returns a divide by zero ? It could be :

sum(TotalCount),
RetailDen,
SQRT(((SUM(Flowthru) + RetailNum)
...

Before casting anything, I would look for the origin of the problem.|||for example you have

select (SUM(Flowthru)/SUM(TotalCount)) * 100 AS CELCFtPct from table

select CELCFtPct = case when SUM(TotalCount)) = 0 then 0
else (SUM(Flowthru)/SUM(TotalCount)) * 100 end
from table

You can do that for all your divisions so you do not get division by zero error. Not sure what field you needed this on but its a good ideal to do this anytime that you are dividing because at some point you are bound to divide by zero.

Hope that helps

KG|||Thank you for your help.. I figured out that my data types needed to be changed to float. I had it a varchar.

Joe

Division by zero and computed by column

I've got two integer columns in the table, third one is computed by previous two ones division. That's fine, however sometimes can happen that divided by column is set to zero. How can i avoid division by zero exception, please? TIA

The best way to avoid the problem is to use a CASE statement like:

declare @.aTable table(value1 int, value2 int)
insert into @.aTable values (32, 4)
insert into @.aTable values (5, 0)

select value1,
value2,
case when value2 <> 0 then value1/value2 end
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0 NULL
*/

-- Or maybe:

select value1,
value2,
isnull(convert(varchar(11), case when value2 <> 0 then value1/value2 end), '')
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0
*/

|||

Thank you for your reply.

So you prefer to get the result via select command instead of fixed computed by column, right?

|||I am not sure that I understand that last question; what exactly do you mean?|||

You can use the CASE structure in a computed column definition:

Something like:

Code Snippet

ALTER TABLE MyTable

ADD COLUMN MyComputedCol AS ( CASE WHEN ( [Col1] <> 0 ) THEN ( [Col2] / [Col1] ) END )

|||Thanks for picking me up, Arnie! Again, I am asleep at the wheel! Sheesh! To answer your question, Jan, no, I have no issue against using the computed column. As Anie indicated, the computed column should be just fine. (I'm BRAINDEAD today!)|||Wow, that's amazing. Thank you Kent and Arnie!

Division by zero

Hi, I have a field in which I use a division. Sometimes the denominator can
be zero. In such case, I want to return 0 instead of evaluating the division
giving me an error.
Here is what I attempted but without success. It appears that both
expressions are evaluated all the time regardless of the result of the
condition.
=iif( Fields!Denominator.Value = 0, 0, Fields!Numerator.Value/
Fields!Denominator.Value)
I would like to know if someone knows a work around to this.
Thanks,
JoeI finally found a way to do it:
=((Fields!Numerator.Value / iif( Fields!Denominator.Value = 0, 1,
Fields!Denominator.Value)) * iif( Fields!Denominator.Value = 0, 0, 1))|||Thats the way I was doing it. But if you have a bunch of these its
gets tedious and I would recommend writing a custom assembly that does
this instead.
Abe
joerage wrote:
> I finally found a way to do it:
> =((Fields!Numerator.Value / iif( Fields!Denominator.Value = 0, 1,
> Fields!Denominator.Value)) * iif( Fields!Denominator.Value = 0, 0,
1))

Division by zero

Hi!

I have a field where i have to do a division. To be sure that the value will never be zero i have done this:

=IIf(Sum(Fields!Total_Amount_1.Value) <>0,
((Sum(Fields!Total_Amount_2.Value)-Sum(Fields!Total_Amount_1.Value)) / Abs(Sum(Fields!Total_Amount_1.Value))),"N/A")

When i run the report i get the "#Error" in the field.

I have done some test and notice that it seems that the problem is that the IIF try both condition (true condition and false condition). When i replace the ABS function by a number like 100 everything is working fine. So the error seems to be the division by zero but this is why i'm using a IIF.

How can i do this division only if the value is not 0 and when it's 0 return "N/A" ?

Thank !
I found a post with the same question as mine:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=188595&SiteID=1

This work fine!

Thanks

Division

Newbie Question.
While using a query/subquery to get a percentage I keep getting zero. I
assume this is becuase the number returned is less than 1. How can I make
this work right?
Example
Select 123/12345
returns 0 when it should equal 0.00996
I have tried converting using decimal(5,5) but either this is wrong, or I am
doing it wrong.
Thanks
DaveThe result of an integer division will be integer.
Example:
select 4 / 2, 4.00 / 2, 4 / 2.00, cast(2 as numeric(5, 2)) / 4
AMB
"Dave S." wrote:

> Newbie Question.
> While using a query/subquery to get a percentage I keep getting zero. I
> assume this is becuase the number returned is less than 1. How can I make
> this work right?
> Example
> Select 123/12345
> returns 0 when it should equal 0.00996
> I have tried converting using decimal(5,5) but either this is wrong, or I
am
> doing it wrong.
> Thanks
> Dave
>
>|||Select 123/(12345 * 1.0)
Keith
"Dave S." <davidstedman@.colliergov.net> wrote in message
news:uUS4OO5EFHA.1932@.TK2MSFTNGP14.phx.gbl...
> Newbie Question.
> While using a query/subquery to get a percentage I keep getting zero. I
> assume this is becuase the number returned is less than 1. How can I make
> this work right?
> Example
> Select 123/12345
> returns 0 when it should equal 0.00996
> I have tried converting using decimal(5,5) but either this is wrong, or I
am
> doing it wrong.
> Thanks
> Dave
>

Divided by zero

=IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
Fields!GOL_DAILYRUNRT.Value * 100, 0)
Why this is throwing Divided by zero error
please help meBecause GOL_DAILYRUNRT.Value is zero? Perhaps you should put
IIF( Fields!GOL_DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
Fields!GOL_DAILYRUNRT.Value * 100, 0)?
MC
"PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> Fields!GOL_DAILYRUNRT.Value * 100, 0)
> Why this is throwing Divided by zero error
> please help me|||As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
check if that is greater than zero.
Try this:
=IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
In any case, IIF is a function call and therefore all arguments are
evaluated before the function is invoked( which causes the division by zero
in expression).
"PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> Fields!GOL_DAILYRUNRT.Value * 100, 0)
> Why this is throwing Divided by zero error
> please help me|||You will need another ) at the end:
=IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value))
I have tried your expression as it is a more elegant solution to this
nagging problem than I have been using. Of course in my tests this morning I
could not get the simple expression IIF(FieldB.Value = 0, 0, FieldA.Value /
FieldB.Value) to fail. It returned zero instead of #error, NaN, infinity....
It seems as though RS evealuate both sides of the IIF statement sometimes
and only one side on other occassions. There seems to be no consistency at
all that I can figure out, however, I will definitely try your expression the
next time I need to code around this issue. Thanks for the posting.
"RA" wrote:
> As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
> check if that is greater than zero.
> Try this:
> =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
> In any case, IIF is a function call and therefore all arguments are
> evaluated before the function is invoked( which causes the division by zero
> in expression).
> "PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
> news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> > =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> > Fields!GOL_DAILYRUNRT.Value * 100, 0)
> > Why this is throwing Divided by zero error
> > please help me
>
>|||Note: IIF() is a function call. Therefore, all arguments get evaluated by
the CLR before the function is called. See also:
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> wrote in
message news:31975EF8-B448-4748-85DD-FBFFF54DB634@.microsoft.com...
> You will need another ) at the end:
> =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value))
> I have tried your expression as it is a more elegant solution to this
> nagging problem than I have been using. Of course in my tests this morning
I
> could not get the simple expression IIF(FieldB.Value = 0, 0, FieldA.Value
/
> FieldB.Value) to fail. It returned zero instead of #error, NaN,
infinity....
> It seems as though RS evealuate both sides of the IIF statement sometimes
> and only one side on other occassions. There seems to be no consistency at
> all that I can figure out, however, I will definitely try your expression
the
> next time I need to code around this issue. Thanks for the posting.
>
> "RA" wrote:
> > As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
> > check if that is greater than zero.
> >
> > Try this:
> > =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> > Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
> >
> > In any case, IIF is a function call and therefore all arguments are
> > evaluated before the function is invoked( which causes the division by
zero
> > in expression).
> >
> > "PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
> > news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> > > =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> > > Fields!GOL_DAILYRUNRT.Value * 100, 0)
> > > Why this is throwing Divided by zero error
> > > please help me
> >
> >
> >|||Consider using CUSTOM CODE (look it up in the help docs) that can
globally solve this for your whole report with a function like this:
Function SafeDivide(byval onumer as Object, byval odenom as Object) as
Decimal
' author: jerry nixon
' purpose: divide and avoid div by zero errs
' version: 1.4
If onumer Is Nothing Then Return 0
If odenom Is Nothing Then Return 0
If isDbNull(onumer) Then Return 0
If isDbNull(odenom) Then Return 0
Dim inumer As Decimal
Dim idenom As Decimal
Try
inumer = Ctype(onumer, Decimal)
idenom = Ctype(odenom, Decimal)
Catch ex as Exception
Return 0
End Try
If Not isNumeric(inumer) Then Return 0
If Not isNumeric(idenom) Then Return 0
If idenom = 0 Then Return 0
Try
Return Decimal.Divide(inumer, idenom)
Catch ex As Exception
Return 0
End Try
End Function

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)
What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:

> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>
|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.
|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegr oups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
--
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:
> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegroups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

Divide by zero with Sub Query

Hello,
I need to dived a number by a summed value from a subquery. Any
suggestions on how to trap for zeros would be appreciated.
--
Thanks in advance,
sck10
SELECT
tsp01.StoreRevenue /
SELECT SUM(tft02.TotalFunded)
FROM
MyTable Tsp02
WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
GROUP BY Tsp02.Track_ID)What do you want to do if you have found a 0?
Try
select 20 / nullif(2, 0) -- returns 10
select 20 / nullif(0, 0) -- returns NULL
Ben Nevarez, MCDBA, OCP
Database Administrator
"sck10" wrote:

> Hello,
> I need to dived a number by a summed value from a subquery. Any
> suggestions on how to trap for zeros would be appreciated.
> --
> Thanks in advance,
> sck10
> SELECT
> tsp01.StoreRevenue /
> SELECT SUM(tft02.TotalFunded)
> FROM
> MyTable Tsp02
> WHERE (Tsp02.Track_ID = Tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID)
>
>|||SELECT
CASE
WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
END
FROM tsp01, tsp02
where (tsp02.Track_ID = tsp01.Track_ID)
GROUP BY Tsp02.Track_ID
let me know if this works for you.|||Thanks Zomer,
This worked...
"zomer" <noneee@.gmail.com> wrote in message
news:1143485957.082088.145680@.i39g2000cwa.googlegroups.com...
> SELECT
> CASE
> WHEN SUM(tft02.TotalFunded)=0 THEN 0 ELSE
> SUM(tsp01.StoreRevenue)/SUM(tft02.TotalFunded)
> END
> FROM tsp01, tsp02
> where (tsp02.Track_ID = tsp01.Track_ID)
> GROUP BY Tsp02.Track_ID
> let me know if this works for you.
>

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

divide by zero exception

How I can rectify divide by zero exception in stored procedure

By checking first, to make sure you're not about to divide by zero ? I suspect a CASE statement is the easiest way to do this.

|||i know it, but actually I want that display 0 or my error message instead of server error message.|||

OK - so what do you need to know ? In your stored proc, return the result of the divide if it's not going to divide by zero, or zero if it is. Then you can check for 0 and show an error if you prefer.

|||

when i write Query like

select 4/0 then it display error msg - 8134

but i don't wan't to display it, i want it as 0.

i have too much table like apr0405i, aprs0506i similary as years wise table. So i create sp and passing '0405' value of parameter of sp which select the table of corresponding year then it display value. but in 0506 then it display the divide by zero exception. so i want to suppress this error message and display 0. because it can't possible to use case with big sql query since i am using more than 15 sql query in one query statement and stored that value in parameter & then print value of paramter

|||

OK - so your database design is not so great ? You create a table for each month ?

I don't know of any way to make SQL Server return 0 when you divide by 0.

|||Joining this thread a little late, oh well....

As far as I am aware, you can't make SQL Server return 0 but you can override the default behaviour (suppressing error messages and the like) and define your own behaviour, such as returning 0. For example:

/*
Override default behaviour
*/
SET ARITHABORT OFF -- Divide by 0 does not halt execution
SET ARITHIGNORE ON -- Divide by 0 does not display error message
SET ANSI_WARNINGS OFF -- Divide by 0 does not display error message

DECLARE @.returnOfCalc As INT

SELECT @.returnOfCalc = 4/0
IF @.returnOfCalc IS NULL
BEGIN
SET @.returnOfCalc = 0
PRINT @.returnOfCalc -- Optional, just as a demo of what can be done
END

/*
Restore default behaviour
*/
SET ARITHABORT ON
SET ARITHIGNORE OFF
SET ANSI_WARNINGS ON

So in your stored procedure, you would issue the first bunch of SET statements at the beginning of the procedure (to override default behaviour) and just before the end of the stored procedure you would issue the second set of SET statements (to restore the default behaviour).

I really don't suggest you mess with the default behaviour in SQL Server, but if you really want/need to then knock yourself out. Just remember to be a good citizen and reset the default behaviour when you finish.

Hope that helps a bit, but sorry if it doesn't
|||

You can do something like:

select coalesce(@.i/nullif(@.j, 0), 0)

-- or

select case @.j when 0 then @.j else @.i/@.j end

|||

use the try... catch

feature of sql2k5

|||

Thanks for ur suggestion, but I already solved it by

case method it goes too much bigger but it gives satisfied results

well, ur answer giving very well theoritical concept, Thank u once again, Next I will send u another query, if i have

|||Thank U chandra ji once again, i was already using same ideas which u send. But Its very well felling inside me, Thanks once again

Divide by zero error?

I have several situations where I encounter the following error:
[Microsoft][ODBC SQL SERVER DRIVER][SQL Server]Divide by zero error
encountered.
I'm sure this topic has been covered hundreds of times - but how would I
stop this error in the following query:
SELECT [Pharmacy Exp] / TotalMem AS PhExpPMPM
FROM Sheet1$
WHERE ([Pharmacy Exp] <> '') AND (Period = '3Q04') AND (Domicile LIKE
'%ct%' OR
Domicile LIKE '%ma%' OR
Domicile LIKE '%nh%' OR
Domicile LIKE '%me%' OR
Domicile LIKE '%ri%' OR
Domicile LIKE '%vt%' OR
Domicile LIKE '%ny%' OR
Domicile LIKE '%nj%')
This issues comes about because either [pharmacy exp] or TotalMem is either
null or zero. I don't want to exclude the data from my query (ie: using
where [pharmacy exp] <>'') but would rather show a zero for the data point.SELECT CASE (TotalMem WHEN 0 THEN 0
ELSE [Pharmacy Exp] / TotalMem END PhExpPMPM
FROM Sheet1$
WHERE ([Pharmacy Exp] <> '') AND (Period = '3Q04') AND (Domicile LIKE
'%ct%' OR
Domicile LIKE '%ma%' OR
Domicile LIKE '%nh%' OR
Domicile LIKE '%me%' OR
Domicile LIKE '%ri%' OR
Domicile LIKE '%vt%' OR
Domicile LIKE '%ny%' OR
Domicile LIKE '%nj%')
Jacco Schalkwijk
SQL Server MVP
"William" <da@.northernit.net> wrote in message
news:KlU3e.6431$6a5.1685@.twister.nyroc.rr.com...
>I have several situations where I encounter the following error:
> [Microsoft][ODBC SQL SERVER DRIVER][SQL Server]Divide by zero error
> encountered.
> I'm sure this topic has been covered hundreds of times - but how would I
> stop this error in the following query:
>
> SELECT [Pharmacy Exp] / TotalMem AS PhExpPMPM
> FROM Sheet1$
> WHERE ([Pharmacy Exp] <> '') AND (Period = '3Q04') AND (Domicile LIKE
> '%ct%' OR
> Domicile LIKE '%ma%' OR
> Domicile LIKE '%nh%' OR
> Domicile LIKE '%me%' OR
> Domicile LIKE '%ri%' OR
> Domicile LIKE '%vt%' OR
> Domicile LIKE '%ny%' OR
> Domicile LIKE '%nj%')
> This issues comes about because either [pharmacy exp] or TotalMem is
> either
> null or zero. I don't want to exclude the data from my query (ie: using
> where [pharmacy exp] <>'') but would rather show a zero for the data
> point.
>

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 error SQL SERVER

Hi

Is there a way to prevent this error?

I'm getting it from query line:

10000 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial)) / (22 * 6 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial)),1) 'Atingimento Semestre'

Thanks a lot

I found out how, thanks:

CASE WHENcount(filial_cotacao) = 0 THEN 0 ELSE
(10000 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial)) / (22 * 6 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial))ENDAS 'Atingimento Semestre'

|||

In SQL Server 2005 the rules for ArithAbort and ArithIgnore have changed check the links below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms184341.aspx

http://msdn2.microsoft.com/en-us/library/ms190306.aspx

Divide by zero error only with top

In the following expression, the query executes with no errors (there are sometimes 0's and nulls in IRON and ENERGY):

SELECT * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by IRON/ENERGY desc

but when TOP is added there is a divide by zero error. Is there a way to avoid this?

SELECT TOP(64) * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by IRON/ENERGY desc

Thanks.

This works but perhaps there is something more elegant:

select top (24) a.*, b.IRON/b.ENERGY as IRON2ENERGY

FROM gff2vg a,(SELECT * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0) ) b

WHERE a.UNIQID=b.UNIQID

order by IRON2ENERGY DESC

|||

If the field contains 0 or Null to avoid the divide by zero error always use case statement like this

SELECT TOP(64) * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by CASE ISNULL(ENERGY,'') WHEN 0
WHEN '' THEN 1
ELSE IRON/ENERGY
END desc

|||

The error has to do with SQL Server evaluating the expression IRON/ENERGY before the WHERE clause. There is no guarantee on the order in which various expressions in a SELECT statement (WHERE clause, SELECT list, ORDER BY, GROUP BY etc) are evaluated. See the link below for more details on the behavior change in SQL Server 2005:

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

So the correct way to write the query is like:

SELECT *

FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by IRON/nullif(ENERGY, 0) desc

-- or

SELECT TOP(64) * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by IRON/nullif(ENERGY, 0) desc

|||I only unmarked this as a solution because I am not sure of the nullif in the case ENERGY = null behavior and a null in IRON. The original intention is to get a recordset with no records that have IRON = null or any records with ENERGY = null or 0. In "ORDER BY IRON/nullif(ENERGY, 0)", if ENERGY = NULL or ENERGY=0->NULL via nullif(ENERGY,0), does IRON/null necessarily exclude the record? Also, if IRON = NULL, then the record is not of use either.|||

The expression in the ORDER BY clause has nothing to do with the predicates. It just ensures that you don't get overflow error in case of 0. NULL values of energy or iron will get filtered because of your WHERE clause. See Books Online for more details on what NULLIF does and how comparisons against NULL are handled. Using NULLIF is the easiest way to prevent divide by zero errors since it will result in the expression being NULL. You can use COALESCE around the entire expression if you want to assign a different default value like "coalesce(iron/nullif(energy, 0), -1).

|||Thank you for the explanation.