Divide by zero error - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Divide by zero error

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-24 : 15:04:33

Hello,

When I execute the following query, I get a Divide by zero error. I believe the Divide By zero error is occuring in the following statement. How do I avoid the error?

ModelDiff = CONVERT(decimal (8, 4), abs(yest.ModelValue-today.ModelValue)/abs(yest.ModelValue))

drop table #MaxArchiveDates
DECLARE @MaxArchiveDate INTEGER
, @EdgeModelID INTEGER
, @DefinitionID INTEGER
SELECT ArchiveDataDate = MAX(ArchiveDataDate) INTO #MaxArchiveDates FROM intldbArchive.dbo.GOModelUniversesSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOSecurityGroupModelValSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOSecuritiesSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOUniverseSecurityMembeSaveD
SELECT @MaxArchiveDate = MIN(ArchiveDataDate) FROM #MaxArchiveDates
SELECT @MaxArchiveDate
SELECT ModelID = today.ModelID
, RelativeUniverseID = today.RelativeUniverseID
, SecurityID = today.SecurityID
, ModelValueT = CONVERT(decimal (8, 4), today.ModelValue)
, ModelValueY = CONVERT(decimal (8, 4), yest.ModelValue)
, ModelDiff = CONVERT(decimal (8, 4), abs(yest.ModelValue-today.ModelValue)/abs(yest.ModelValue))
FROM OptMod.dbo.GO_SecurityGroupModelValues today
JOIN intldbArchive.dbo.GOSecurityGroupModelValSaveD yest
ON today.SecurityID = yest.SecurityID
AND today.ModelID = yest.ModelID
AND yest.ArchiveDataDate = @MaxArchiveDate

Thanks in advance!!!
sqlnovice123

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-24 : 15:15:24

If u want the results when yest.ModelValue = 0 as well, u should know what u expect.
If u don't want the results in case yest.ModelValue = 0, then u can add that as the where clause condition as :

........
.......
, ModelValueY = CONVERT(decimal (8, 4), yest.ModelValue)
, ModelDiff = CONVERT(decimal (8, 4), abs(yest.ModelValue-today.ModelValue)/abs(yest.ModelValue))
FROM OptMod.dbo.GO_SecurityGroupModelValues today
JOIN intldbArchive.dbo.GOSecurityGroupModelValSaveD yest
ON today.SecurityID = yest.SecurityID
AND today.ModelID = yest.ModelID
AND yest.ArchiveDataDate = @MaxArchiveDate
Where yest.SecurityID  != 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources