SQL Server Performance

LOG function returns "A domain error "

Discussion in 'General Developer Questions' started by SanetteWessels, Jun 9, 2005.

  1. SanetteWessels New Member

    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
  2. dineshasanka Moderator

  3. SanetteWessels New Member

    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
  4. dineshasanka Moderator

    DECLARE @var float
    SET @var = 5
    if @var > 0
    SELECT LOG(@var)
  5. FrankKalis Moderator

    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
  6. dineshasanka Moderator

    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
  7. SanetteWessels New Member

    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
  8. FrankKalis Moderator

    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 />
  9. FrankKalis Moderator

    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
  10. SanetteWessels New Member

    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
  11. dineshasanka Moderator

    Where clause also possible

Share This Page