Post reply

Domain error when executing user defined function

  • August 1, 2008 at 1:38 pm

    #188866

    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

    #852179

    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