August 1, 2008 at 1:38 pm
I have this sql query (my User defined function, it takes string parameter and returns a table with calculated value for this string parameter, it uses 2 tables to get values for calculation and creates one temporary table "DFreqTable" to store temporary needed values):
use AR;
go
CREATE FUNCTION EntFunction
(@ItemName varchar(100))
returns @EntTable table (
EffName varchar(100) NOT NULL,
EntValue float NOT NULL)
as
BEGIN
DECLARE @TransactionNumber int;
DECLARE @DFreqTable table(
DName varchar(100) NOT NULL,
DFreq int NOT NULL);
SELECT @TransactionNumber = (SELECT count(TransactionID) FROM AR.dbo.EffectsTransactions WHERE EffectName=@ItemName)
INSERT INTO @DFreqTable
SELECT
dr.DName,
count(dr.DName)/@TransactionNumber AS DFreq
FROM
AR.dbo.DTransactions dr, AR.dbo.EffectsTransactions ef
WHERE
ef.EffectName=@ItemName
AND dr.TransactionID=ef.TransactionID
GROUP BY dr.DName
INSERT INTO @EntTable
SELECT @ItemName, sum(DFreq*LOG(DFreq)) FROM @DFreqTable
RETURN
END
I executed it as sql query and obtained new dbo object in my Object explorer (in Programmability->Functions)
Now I create new simple query:
select EntValue from AR.dbo.EntFunction('ABASIA');
and I get this error instead of float value of "EntValue" field after execution:
"A domain error occurred."
it is first UDF, I've written.
What is it? What I do wrong?
August 1, 2008 at 2:27 pm
Hmm, I've got a QotD coming out next week and I'm afraid that my answer here, might give it away. Oh, well, helping is the first priority... 🙂
You are trying to take the LOG() of an invalid value, specifically zero (0), which is invalid for the LOG function. This is because your DFreq value is sometimes zero.
Your DFreq value is sometimes zero because it comes from the expression "count(dr.DName)/@TransactionNumber". @TransactionNumber is an integer. COUNT() is a function that returns an integer. If you divide an integer by a larger integer, you get zero (because it truncates down to the next lowest integer).
I would suggest that you change one of these operands to floating-point before the division:
count(dr.DName)/CAST(@TransactionNumber as float)
[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]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply