Hi everyone, We have a calculation that uses the LOG function and fails with a "Domain Error". After investigating the calculation it was found it calculates the LOG of 0.00. How can one trap for this error? Here is a simple example of the problem: DECLARE @var float SET @var = 0.00 SELECT LOG(@var) Thanks in advance!! Kind regards Sanette SQL Developer JustEnough Software Corporation Walking on water and coding of a spec is easy, as long as both are frozen - A friend

There is no value for Log 0 . try this in calculator , it says Invalid Input for function http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/Performance.asp for more infomation

Yes I know, but how can I trap for this? SQL Developer JustEnough Software Corporation Walking on water and coding of a spec is easy, as long as both are frozen - A friend

To explain a bit. LOG(0), LOG(-1) and SQRT(-1) all cause domain errors. Mathematically they can be calculated, but that would push you into the realm of complex numbers, which SQL Server simply cannot represent. -- Frank Kalis SQL Server MVP http://www.insidesql.de

quote:Originally posted by FrankKalis To explain a bit. LOG(0), LOG(-1) and SQRT(-1) all cause domain errors. Mathematically they can be calculated, but that would push you into the realm of complex numbers, which SQL Server simply cannot represent. How can you calculate Log(0) Log is define a way such Log (x) where x > 0, isn't it??? This is out of SQL Server

Thanks for all the replies. Please find the sql code where I have this problem. Maybe this will shed some light on my problem. It sometimes happen (not always) that the calculation before calculating the LOG works out to be 0.00. I will not be able to check for @var > 0. UPDATE SV SET SafetyStockUnitsRD = (-LOG(EXP((PI()/(SQRT(3.00)*LTDemDeviationqDDLTRD))*IP.ReplenishmentCycleDays*(1-IP.ServiceLevel))-1))/((PI()/(SQRT(3.00)*LTDemDeviationqDDLTRD))) FROM dbo.ctbl_SpokeVariables SV INNER JOIN dbo.udf_ApplyFilter(@iFilterID,1,1) AF ON AF.ForeignKeyID = SV.InventoryID INNER JOIN dbo.tbl_InventoryPolicy IP ON IP.InventoryID = SV.InventoryID WHERE (SQRT(3.00)*LTDemDeviationqDDLTRD) > 0 SQL Developer JustEnough Software Corporation Walking on water and coding of a spec is easy, as long as both are frozen - A friend

Okay, LOG is only defined for positive numbers. Bad example [<img src='/community/emoticons/emotion-6.gif' alt='' />]<br />But as I've said, it can be calculated for negative numbers. The result is a complex number, which is definitely outside SQL Server.<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />

quote:Originally posted by SanetteWessels Thanks for all the replies. Please find the sql code where I have this problem. Maybe this will shed some light on my problem. It sometimes happen (not always) that the calculation before calculating the LOG works out to be 0.00. I will not be able to check for @var > 0. UPDATE SV SET SafetyStockUnitsRD = (-LOG(EXP((PI()/(SQRT(3.00)*LTDemDeviationqDDLTRD))*IP.ReplenishmentCycleDays*(1-IP.ServiceLevel))-1))/((PI()/(SQRT(3.00)*LTDemDeviationqDDLTRD))) FROM dbo.ctbl_SpokeVariables SV INNER JOIN dbo.udf_ApplyFilter(@iFilterID,1,1) AF ON AF.ForeignKeyID = SV.InventoryID INNER JOIN dbo.tbl_InventoryPolicy IP ON IP.InventoryID = SV.InventoryID WHERE (SQRT(3.00)*LTDemDeviationqDDLTRD) > 0 SQL Developer JustEnough Software Corporation Walking on water and coding of a spec is easy, as long as both are frozen - A friend At a quick glance, you can have a CASE expression -- Frank Kalis SQL Server MVP http://www.insidesql.de

Thanks Frank! Will try that. Sanette SQL Developer JustEnough Software Corporation Walking on water and coding of a spec is easy, as long as both are frozen - A friend