Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Workaround for SQL-view 'divide by zero' error

0 views
Skip to first unread message

Martijn

unread,
Apr 2, 2003, 3:59:43 PM4/2/03
to
One of my SQL2000 views can generate a 'zero'.
I want the view to return a '0' if the division by zero-error occurs
(instead of an error)...

Any help would really be appreciate...!


The View (if 'ObjDatum' is today than the division between today's
date -getdate- and ObjDatum equals 'zero'):
SELECT TOP 1 TotalVisits / DATEDIFF(d, ObjDatum, GETDATE()) AS
HighestAverage
FROM SQLtable.StatsQuery
ORDER BY TotalVisits


The error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Divide by zero error
encountered.


John Gilson

unread,
Apr 2, 2003, 4:23:38 PM4/2/03
to
"Martijn" <martijn.h@home..-remove.this.part-...nl> wrote in message
news:b6fivt$4uv$1...@news4.tilbu1.nb.home.nl...

SELECT TOP 1 COALESCE(TotalVisits /
NULLIF(DATEDIFF(d, ObjDatum, GETDATE()), 0), 0)


AS HighestAverage
FROM SQLtable.StatsQuery
ORDER BY TotalVisits

Regards,
jag


Martijn

unread,
Apr 2, 2003, 4:39:05 PM4/2/03
to
you're the greatest :-)
thanx.
0 new messages