Divide by '0' , IsNull is ignored PLEASE HELP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Divide by '0' , IsNull is ignored PLEASE HELP

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-14 : 09:48:55

Hello friends,
I created a view and when I view the data on Ent. Manger it works just fine. When I view the data on QA, this is the error:
(200 rows affected)
Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operations.
Below is my view, the view is successfully created.
Please help.
Jeny.

SELECT p.p_num,
c.c,
c.c_id,
co.co_na,
co.co_id,
p.p_Title,
p.day_Start,
p.day_End,
ISNULL(rate,0)*ISNULL(fu_p_inc.total,0) AS inc,
ISNULL(fu_p_job.tot_money,0) AS tot_val,
fu_p_inc.MaxOfinc_Period_To,
co_Type.co_Type,
POC.POC_na,
co_day_Start.MinOfday_Start_Date,
co_day_End.MaxOfday_End_Date
FROM (dept_div_na INNER JOIN (((co_Type RIGHT JOIN (c
RIGHT JOIN (coing_off RIGHT JOIN co ON
coing_off.coing_off_ID = co.coing_off_ID) ON
c.c_ID = co.c_ID) ON
co_Type.co_Type_ID = co.co_Type_ID) LEFT JOIN
co_day_Start ON co.co_ID = co_day_Start.co_ID) LEFT JOIN
co_day_End ON co.co_ID = co_day_End.co_ID) ON
dept_div_na.Division_ID = co.Division_ID) LEFT JOIN
(((p LEFT JOIN fu_p_inc ON
p.p_ID = fu_p_inc.p_ID) LEFT JOIN
fu_p_job ON p.p_ID = fu_p_job.p_ID) LEFT JOIN
POC ON p.tech_p_ID = POC.POC_ID) ON co.co_ID = p.co_ID

The stupid question is the question you don't ask.
www.single123.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-04-14 : 10:05:20

I'm confused...don't you need a division of some sort to get this error...?? The join was rather nasty but I can't seem to find any divisions...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-14 : 10:06:54

"can't seem to find any divisions..."

...Unless some part of the query is a function???

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-14 : 10:11:18

regarding QA vs EM, from EM try returning all rows (or top 201 records). I think the error wasn't encountered for just the number of records returned in EM. In QA all records were processed.

Are any of the tables involved in your (incredibley complex) From clause actually views? Since there is no division involved in this view, my guess is it's happening "upstream" somewhere. You also got the "null value is eliminated" warning, and you have no aggregations happening in this select statement. So I suspect one of your views in the from clause is where the error is happening.

Be One with the Optimizer
TG

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-14 : 10:20:05

I found out this is the one that generates error, if I take it off, it works.
ISNULL(rate,0)*ISNULL(fu_p_inc.total,0) AS inc,

The stupid question is the question you don't ask.
www.single123.com

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-14 : 10:20:20

Was this not solved here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48363

I'm not sure why there is another thread..

-------
Moo. :)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-04-14 : 10:25:28

I'm no genious but for a multiplication to return a divide by zero error seems pretty far out. There is definetly something more to this than what you are giving us.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-14 : 10:32:14

What is fu_p_inc? can you do this without an error: Select total from fu_p_inc ?

btw, are all the "FU" prefixes some sort of rebellion against the "establisment"?

Be One with the Optimizer
TG

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2005-04-14 : 10:35:02

lol TG: "are all the "FU" prefixes some sort of rebellion against the "establisment"

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-14 : 10:39:29

mr mist, big thanks. It worked now. I went back the view before it and used CASE as you said to correct the divide by zero, and it worked.
Thanks,
Jenny.

The stupid question is the question you don't ask.
www.single123.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources