Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: SQL match mocks me

    DECLARE @First NUMERIC(38,20)

    DECLARE @Second NUMERIC(38,20)

    SELECT @First = 3763.88176470588235294117,@Second = 1.00

    SELECT @First

    SELECT @Second

    SELECT @First * @Second

    SOAB
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    SOAB
    SQL Server says:
    Could not find stored procedure 'SOAB'.

    try SELECT CAST(@First * @Second AS NUMERIC(38,20))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    That still rounds when I try it...is that the issue we are discussing here?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I read about the issue. apparently multiplication and division beyond a certain size with the numeric data type starts doing this. Something about preserving the accuracy on the left side over the right side of the decimal point.

    my real problem looked like 15 levels of (((this * that) * thisotherthing - who) ) etc....)

    What I ended up doing was casting the result of each inner set of parens to a more reasonable data type while maintaining my accuracy of the 20th decimal place which was my issue. I love being off by a fraction of a penny.

    I will find out if my 700,000 lineitems balance on friday but I think I am going to be alright.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I think stuff like this is what made people initially think that they could make money by transferring the fractions of a penny to a personal bank account
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what kind of gets me about the whole thing, besides the absurdly large data type these folks are using, is that if SQL Server does not like numerics abover a certain size, it would seem to make sense to me to throw a syntax error or warning when trying to declare a couple of numeric(38,20)s.

    that is my very small fraction of 2 cents on it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •