Professional Web Applications Themes

SQL Views and rounding ( * 1.0000000000) - Microsoft SQL / MS SQL Server

SQL Server by default i suppose rounds values in a view. Maybe to 6 digits? I am not sure. I am talking about rounding numbers that are a calculated value. What I have been doing to get more decimals afer the decimal place is something like this.... field1 * field2 * 1.000000000000 as fieldTotal this seems to work... although on one field it won't. This field is based on several other calculated fields in a subquery (which I also use the * 1.0000000000 syntax)... My issue is that it keeps returning as an example 0.002156 Instead of the proper value ...

  1. #1

    Default SQL Views and rounding ( * 1.0000000000)

    SQL Server by default i suppose rounds values in a view. Maybe to 6 digits? I am not sure. I am talking about rounding numbers that are a calculated value.

    What I have been doing to get more decimals afer the decimal place is something like this....

    field1 * field2 * 1.000000000000 as fieldTotal

    this seems to work... although on one field it won't. This field is based on several other calculated fields in a subquery (which I also use the * 1.0000000000 syntax)...

    My issue is that it keeps returning as an example 0.002156

    Instead of the proper value which is 0.0021555

    This is important as it noticably throws of the accuracy of the results. I have a subquery that uses this number as a calculation. It makes it very noticeable.

    What is the issue here? Why do I have to put up with this ? Ugggg!

    Is it because maybe I have reached a maximum record length ? I cant see this being an issues though as a max record length is 8000 or so... I should be nowhere near that...

    Any idea on what to do?


    Dan Guest

  2. #2

    Default Re: SQL Views and rounding ( * 1.0000000000)

    Why don't you just CAST(decimal, 15,10) or so to get the precision you want?

    Be careful if your are using float or real. Those datatypes are not precise and rounding errors are possible, and if you multiply an integer or decimal with a float, the result will be automatically converted to a float. If accuracy is important, cast everything to a decimal datatype first.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Dan" <kjllkjlklkjlkjlkj.com> wrote in message news:utIYGtxRDHA.2196TK2MSFTNGP11.phx.gbl...
    SQL Server by default i suppose rounds values in a view. Maybe to 6 digits? I am not sure. I am talking about rounding numbers that are a calculated value.

    What I have been doing to get more decimals afer the decimal place is something like this....

    field1 * field2 * 1.000000000000 as fieldTotal

    this seems to work... although on one field it won't. This field is based on several other calculated fields in a subquery (which I also use the * 1.0000000000 syntax)...

    My issue is that it keeps returning as an example 0.002156

    Instead of the proper value which is 0.0021555

    This is important as it noticably throws of the accuracy of the results. I have a subquery that uses this number as a calculation. It makes it very noticeable.

    What is the issue here? Why do I have to put up with this ? Ugggg!

    Is it because maybe I have reached a maximum record length ? I cant see this being an issues though as a max record length is 8000 or so... I should be nowhere near that...

    Any idea on what to do?



    Jacco Schalkwijk Guest

Similar Threads

  1. Rounding off
    By Mike Blezien in forum PERL Beginners
    Replies: 1
    Last Post: November 22nd, 11:22 PM
  2. Rounding Up
    By Brent Bortnick in forum ASP
    Replies: 14
    Last Post: October 8th, 04:19 AM
  3. how to keep decimals from rounding
    By Rob Yelvington in forum PHP Development
    Replies: 3
    Last Post: August 26th, 04:58 PM
  4. rounding buttons
    By mdjmsmith webforumsuser@macromedia.com in forum Macromedia Fireworks
    Replies: 3
    Last Post: July 20th, 12:06 PM
  5. Replies: 4
    Last Post: July 8th, 07:00 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139