LOG Function - 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

SQL Server 2005 Forums

Transact-SQL (2005)

LOG Function

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2009-08-25 : 08:59:43

Hi All,

I am trying to create a log function to work in a particular way, with a base of 1.15.

---------------------------------------------------------------------
create function mylog(@rate float, @payment float, @capital float)
returns float
as
begin
return
LOG(1/(1-((@capital/12*@rate)/@payment)))/LOG(1+@rate)
end
go
---------------------------------------------------------------------

If I use the function with static values that I enter, it works just as I expect.

select dbo.mylog(0.15, 1000, 50000) ------ result 7.0

However, if I try to use the function with my data table, I get an error "MSG 3623 - A domain error occured" - I think this means SQL server is not happy with my calculation?

select id, dbo.mylog(0.15, clients.payment, clients.capital)
from clients

Can anyone advise where I am going wrong?

Many thanks
Humate

quote:
Originally posted by Michael Valentine Jones

It takes real skill to produce something good out of a giant mess.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-25 : 09:18:52

I googled "MSG 3623 - A domain error occured" and got the answer. Re-write your code to ensure dive by zero never occurs and that you are never taking a log of a negative number.

Jim

Humate
Posting Yak Master

101 Posts

Posted - 2009-08-25 : 09:34:45

Thanks Jim. However, I have used my function on a set of data with no zero's, and still face the same message.

i.e

select id, dbo.mylog(0.15, clients.payment, clients.capital)
from clients
where clients.payment >0 and clients.capital >0

Or am I missing something very obvious?

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-25 : 09:51:19

Have you made sure that (1-((@capital/12*@rate)/@payment))) never produces a negative number?

Jim

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-25 : 10:00:26

quote:
Originally posted by jimf

Have you made sure that (1-((@capital/12*@rate)/@payment))) never produces a negative number?

Jim

I believe jimf nailed it there. Domain error is for illegal mathematical equations, such as LOG(-x)

Divide by 0 produces a divide by zero error, not domain error

Humate
Posting Yak Master

101 Posts

Posted - 2009-08-25 : 10:04:22

Of course, I understand now. Yes, in some cases it does. Could you advise how I can handle the data to prevent the error?

I have tried a case statement with partial success.

LOG(1/ CASE WHEN (1-((@capital/12*@rate)/@payment)) < 0 THEN 1 ELSE (1-((@capital/12*@rate)/@payment)) END) / LOG(1+@rate)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-25 : 10:27:17

Try
create function mylog(@rate float, @payment float, @capital float)
returns float
as
begin

DECLARE @LOG float
SET @LOG = 1
IF(@capital/12*@rate)/@payment <= 1
SET @LOG = (LOG(1/(1-((@capital/12*@rate)/@payment)))/LOG(1+@rate) )

RETURN(@LOG)

end

Humate
Posting Yak Master

101 Posts

Posted - 2009-08-25 : 10:44:18

I am still encountering a divide by 0 error using that version Jim, although i'm not sure why. I changed the argument of the first function to include 0, and I was able to run the function against my full dataset without any warnings. It seems to be working as I wanted.

CREATE function [dbo].[mylog](@rate float, @payment float,@capital float)
returns float
as
begin
return

LOG(1/ CASE WHEN (1-((@capital/12*@rate)/@payment)) <=0 THEN 1 ELSE (1-((@capital/12*@rate)/@payment)) END) / LOG(1+@rate)

end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources