Divide By Zero

Question

Post reply

Divide By Zero

scptech

Right there with Babe

Points: 733
More actions
December 7, 2008 at 5:00 pm

#125551

The following query returns 'Divide by zero' error when recurDays = 0;
select * from apptview where
recurDays > 0 and
(DateDiff(dd, apptDateTime, '12/7/2008') > 0) and (
(DateDiff(dd, apptDateTime, '12/6/2008') / recurDays) <
(DateDiff(dd, apptDateTime, '12/7/2008') / recurDays))
and groupSeq = 0 order by staffName, apptDateTime, ru
Since I am explicitly stating "where recurDays > 0", I thought the rest of the 'and' conditions would not be evaluated (like in programming languages).
How can I work around this?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 1

Hi ,

There are at least a couple of options you can use.

1. use a common table expression - http://msdn.microsoft.com/en-us/library/ms190766.aspx

Something a bit like this (untested)....

with cteAppt(

)

As

(select * from apptview where recurDays > 0)

select * from cteAppt where

(DateDiff(dd, apptDateTime, '12/7/2008') > 0) and (

(DateDiff(dd, apptDateTime, '12/6/2008') / recurDays) <

(DateDiff(dd, apptDateTime, '12/7/2008') / recurDays))

and groupSeq = 0 order by staffName, apptDateTime, ru

2. Use another select statement in your from clause

select * from (select * from apptview where recurDays > 0) as a1 where

(DateDiff(dd, apptDateTime, '12/7/2008') > 0) and (

(DateDiff(dd, apptDateTime, '12/6/2008') / recurDays) <

(DateDiff(dd, apptDateTime, '12/7/2008') / recurDays))

and groupSeq = 0 order by staffName, apptDateTime, ru

hope they help.

B

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

ak (12/7/2008)
Since I am explicitly stating "where recurDays > 0", I thought the rest of the 'and' conditions would not be evaluated (like in programming languages).

Actually, very few programming language compilers will consistently implement short-circuiting in the order of expression without special, explicit operators to control it. Highly optimized compilers (such as SQL, VC++, etc.) almost always reorder evaluations before the short-circuiting. And non-optimized languages (VB, etc.) rarely implement short-circuiting on their own.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

That said, the explicit way to express nested conditions in SQL Serve is to use nested CASE expressions:

select *

from apptview

where 1 = CASE WHEN recurDays > 0 Then

CASE WHEN (DateDiff(dd, apptDateTime, '12/7/2008') > 0)

and ((DateDiff(dd, apptDateTime, '12/6/2008') / recurDays) <

(DateDiff(dd, apptDateTime, '12/7/2008') / recurDays))

and groupSeq = 0 Then 1 Else 0 End

ELSE 0 END

order by staffName, apptDateTime, ru

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 4

Try using NULLIF

select * from apptview where

recurDays > 0 and

(DateDiff(dd, apptDateTime, '12/7/2008') > 0) and (

(DateDiff(dd, apptDateTime, '12/6/2008') / NULLIF(recurDays,0)) <

(DateDiff(dd, apptDateTime, '12/7/2008') / NULLIF(recurDays,0)))

and groupSeq = 0 order by staffName, apptDateTime, ru

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

scptech Right there with Babe Points: 733 More actions · Answer 5

Thank you, that works, I would not have thought of it.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Glad we could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]