declare @n float select @n = 12.001 select convert(varchar(20), @n) go isql returned -------------------- 12.000999999999999 Why? MS SQL 6.5 returns 12.001 Thanks Ivan Rubinov

0 |

2/11/2000 12:47:10 PM

This is a multi-part message in MIME format. --------------B8833FC9B364722D034F7974 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit First, when converting from an imprecise numeric (FLOAT, REAL) you should be using the str() function, rather than convert. The floating point datatype cannot store 12.001 exactly. The convert is displaying the value that is actually stored (or rather, the closest representation it can fit into a varchar(20)). The MS SQL 6.5 client appears to be rounding the displayed value. If you use the str function and specify 3 places of precision, it will also display 12.001. Attached is my writeup on the floating point datatype that explains many of the commonly seen issues. -bret Ivan Rubinov wrote: > declare @n float > select @n = 12.001 > select convert(varchar(20), @n) > go > isql returned > -------------------- > 12.000999999999999 > > Why? MS SQL 6.5 returns > 12.001 > > Thanks > Ivan Rubinov --------------B8833FC9B364722D034F7974 Content-Type: text/plain; charset=us-ascii; name="floatingpoint.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="floatingpoint.txt" The floating point datatype was designed to be able to hold a wide range of values and allow fairly rapid arithmatic operations on them, at the expense of absolute accuracy. The exact nature of the imprecision inherent in floating point datatypes is a source of much confusion to many people. This paper attempts to explain that nature of the imprecision. Some aspects of actual floating point implementation have been simplified or ignored (such as the final two's-complement representation). It should be noted that Sybase did not develop the floating point datatype; it is a widely used IEEE standard. C or C++ programs on the same platform as SQL Server will demonstrate the similar floating point behavior [see Question 5 in the Q&A section below]. There are two common standard types of floating point numbers: 4-byte reals and 8-byte doubles. Reals and doubles store values in a similar format: 1 sign bit, <x> exponent bits, and <y> mantissa bits. The only difference is that reals use smaller exponents and mantissas. ------------------------------------------------------------------------- According to the IEEE standard for floating point Real datatypes have a 23 bit mantissa* and a 9 bit exponent (total 32). Double datatypes have a 53 bit mantissa* and an 11 bit exponent (total 64) Some platforms use different standards. A double on a VAX, for instance, uses a 56-bit mantissa*, 8-bit exponent, and 1 sign bit (total 64) *The right-most bit is implicit. It is not actually stored as it is always on. ------------------------------------------------------------------------- The mantissa is a binary representation of the number, each bit representing a power of two. There is an additional implicit bit at the beginning (right hand side) of the number, which is always on. The exponent indicates a power of two that is used to multiply (or shift) the mantissa to represent larger or smaller values. The first bit of the mantissa represents the value 2^<exponent>, the second bit 2^<exponent-1>, etc. After the mantissa bits needed to represent the whole number part of the number have been used, the fractional part of the number is represented with the remaining bits (if any), which have values of negative powers of two.. For the sake of a simple demonstration, imagine an even smaller floating point format with a 12 bits [including one implicit] mantissa, one sign bit, and 4 exponent bits (total 16). # [1]########### #### ^ ^ ^ ^ | | | Exponent | | Mantissa | Virtual bit of Mantissa (always on) Sign bit To represent a number determine "is the number positive or negative?". Set the sign bit if it is negative. Then determine "what is the smallest power of 2 that is larger than the number?". Subtract one from that power to find the exponent of the implicit bit in the mantissa. Store that exponent in the exponent field. Subtract the value of the implicit bit (2^exponent) from the number. Determine if 2^(exponent-1) is larger than the remainder. If so, set the next bit, subtract the value of that bit from the number, and determine if 2^(exponent-2) is larger than the remainder. Repeat until you run out of mantissa bits. For instance, to represent 123.3: The number is positive, so the sign bit is set to 0: 0 [1]########### #### The smallest power of 2 that is larger than 123.3 is 128, or 2^7, so the exponent is 7-1, or 6. 2^6 is 64, the value of the implicit mantissa bit 0 [1]########### 0110 123.3 - 64 is 59.3. 2^5 is 32, which is smaller than 59.3, so the next bit is set 0 [1]1########## 0110 59.3-32 = 27.3. 2^4 is 16, which is smaller than 27.3, so the next bit is set 0 [1]11######### 0110 27.3 - 16 = 11.3. 2^3 is 8, which is smaller than 11.3, so the next bit is set 0 [1]111######## 0110 11.3 - 8 = 3.3. 2^2 is 4, which is larger than 3.3, so the next bit is not set 0 [1]1110####### 0110 3.3 - 0 = 3.3. @^1 is 2, which is smaller than 3.3, so the next bit is set 0 [1]11101###### 0110 3.3 - 2 = 1.3. 2^0 is 1, which is smaller than 1.3, so the next bit is set 0 [1]111011##### 0110 1.3 - 1 = 0.3. 2^-1 is 0.5, which is larger than 0.3, so the next bit is not set 0 [1]1110110#### 0110 0.3 - 0 = 0.3. 2^-2 is 0.25, which is smaller than 0.3, so the next bit is set 0 [1]11101101### 0110 0.3 - 0.25 = 0.05. 2^-3 is 0.125, which is larger than 0.05, so the next bit is not set 0 [1]111011010## 0110 0.05 - 0 = 0.05. 2^-4 is 0.06125, which is larger then 0.05, so the next bit is not set 0 [1]1110110100# 0110 0.05 - 0 = 0.05. 2^-5 is 0.030625, which is smaller than 0.05, so the next bit is set 0 [1]11101101001 0110 This represents the actual value 64 + 32 + 16 + 8 + 2 + 1 + 0.25 + 0.030625 = 123.280625 123.3 - 123.280625 is an error of 0.019375 It may be possible to reduce the error by rounding up to the next larger number that can be represented (ie, add 2^-5). This representation would be 0 [1]11101101001 0110 + 1 0 [1]11101101010 0110 64 + 32 + 16 + 8 + 2 + 1 + 0.25 + 0.06125 = 123.31125 123.3 - 123.31125 is an error of -0.01125 This is a smaller error, so the representation is rounded to 0 [1]11101101010 0110 (123.31125) as the final representation. The standard "real" and "double" floating point formats work exactly the same, except they have wider mantiassas that reduce the magnitude of the potential error, and wider exponents that extend the posssible range of the number. Some frequently asked questions: ---------------------------------------------------------------------- 1) Why doesn't round() work right with floating point? There is garbage in the sixth decimal place when I round a floating point, as in: declare @x real select @x = 123.31 select @x = round(@x,1) select @x go ------------- 123.300003 A: The decimal rounded value of 123.31 is 123.3, but the real datatype cannot store 123.3 exactly. The garbage is due to the inherent imprecision of the floating point format. There is also a display issue: isql by default displays floats with 6 digits after the decimal point. Some front end programs are more intelligent about this: they know how many digits of the number will be accurate and truncate or round the display at that point. You should use the TSQL str() function for better control over the display of floating point data [str() is documented under "String Functions" in the manuals.] For instance, we rounded to 1 decimal place, so there is no need to display past one decimal place: select str(@x,8,1) go -------- 123.3 (1 row affected) ----------------------------------------------------------------------- 2) So just how inaccurate are reals and doubles? 4-byte reals can store a number with a maximum error of (the number) * (2^-23) 8-byte doubles can store a number with a maximum error of (the number) * (2^-53) As a rule of thumb, this means you can expect the first 7 digits of a real to be correct, and the first 15 digits of a double to be correct. After that, you may start seeing signs of inaccuracy or "garbage". ------------------------------------------------------------------------ 3) When I declare a column or variable to be of type float, there is an optional [(precision)] specification. What effect does this have? If precision is < 16, the server will use a 4-byte real. If the precision is >= 16, the server will use a 8-byte double. You can also explicitly tell the server to use type "real" or type "double precision". If you don't specify a precision and use "float", the server will default to a double. The (precision) specification otherwise has no effect. The syntax may seem somewhat pointless, but it is allowed for compatibility with DDL developed for other systems that interpret (precision) differently. ------------------------------------------------------------------------ 4) So floating point only has problems storing fractions, right? Nope. You can see problems in whole numbers, too. For instance, reals have 23 bits in the mantissa, so they will have problems with numbers that require more than 23 bits to represent correctly. The smallest value we see this for is 2^24+1. Reals can store 2^24th with no problem (it only requires the implicit bit being on, and the exponent set to 24, all the other mantissa bits are zeroed), but 2^24+1 requires 22 zero bits and a final one bit following the implicit bit (24 bits total, only 23 available). 1> select power(2,24)-1, power(2,24), power(2,24)+1 2> go ----------- ----------- ----------- 16777215 16777216 16777217 (1 row affected) 1> create table float_test (x real, y real, z real) 2> go 1> insert float_test values (power(2,24)-1, power(2,24), power(2,24)+1) 2> go (1 row affected) 1> select * from float_test 2> go x y z -------------------- -------------------- -------------------- 16777215.000000 16777216.000000 16777216.000000 (1 row affected) Note that the closest representation of 2^24+1 in a real is equal to 2^24 ----------------------------------------------------------------------- 5) I don't see this behavior in my c/c++ program. What's up? You probably aren't looking hard enough. In general, printf() in c and cout in c++ do not print out with enough precision to show the problems. The imprecision is hidden by rounding done by the display process. Try specifying a higher precision, as in these two sample programs: ========================================= For c: ========================================= main() /* Program to demonstrate floating point imprecision */ { float r; double d ; r = 123.3; d = 123.3; printf("As a %d-byte float (real): 123.3 is %48.24f \n", sizeof(r),r); printf("As a %d-byte double: 123.3 is %48.24f \n", sizeof(d),d); } Sample output on Solaris 2.5: alliance1{bret}125: a.out As a 4-byte real: 123.3 is 123.300003051757812500000000 As a 8-byte double: 123.3 is 123.299999999999997157829057 ========================================= For c++: ========================================= #include <iostream.h> #include <iomanip.h> main() -- Program to demonstrate floating point inaccuracy. { int precision; float y; y = 123.3; cout << "123.3 as a float printed with increasing precision" << endl; cout << "-------------------------------------------------------------" << endl; for (precision = 1; precision < 30; precision++) { cout.precision(precision); cout <<precision << " " << y << endl; } double x; x = 123.3; cout << endl; cout << "123.3 as a double, printed with increasing precision" << endl; cout << "-------------------------------------------------------------" << endl; for (precision = 1; precision < 30; precision++) { cout.precision(precision); cout <<precision << " " << x << endl; } } Sample output on Solaris 2.5: alliance1{bret}140: a.out 123.3 as a float printed with increasing precision ------------------------------------------------------------- 1 1e+02 2 1.2e+02 3 123 4 123.3 5 123.3 6 123.3 7 123.3 8 123.3 9 123.300003 10 123.3000031 11 123.30000305 12 123.300003052 13 123.3000030518 14 123.30000305176 15 123.300003051758 16 123.3000030517578 17 123.30000305175781 18 123.300003051757812 19 123.3000030517578125 20 123.3000030517578125 21 123.3000030517578125 22 123.3000030517578125 23 123.3000030517578125 24 123.3000030517578125 25 123.3000030517578125 26 123.3000030517578125 27 123.3000030517578125 28 123.3000030517578125 29 123.3000030517578125 123.3 as a double, printed with increasing precision ------------------------------------------------------------- 1 1e+02 2 1.2e+02 3 123 4 123.3 5 123.3 6 123.3 7 123.3 8 123.3 9 123.3 10 123.3 11 123.3 12 123.3 13 123.3 14 123.3 15 123.3 16 123.3 17 123.3 18 123.299999999999997 19 123.2999999999999972 20 123.29999999999999716 21 123.299999999999997158 22 123.2999999999999971578 23 123.29999999999999715783 24 123.299999999999997157829 25 123.2999999999999971578291 26 123.29999999999999715782906 27 123.299999999999997157829057 28 123.299999999999997157829057 29 123.29999999999999715782905696 --------------------------------------------------------------------- 6) Where can I find more information on floating points? Many books on assembly language programming go into great detail. A search on the World Wide Web for keywords "IEEE" and "floating" will provide many documents of interest. --------------B8833FC9B364722D034F7974--

0 |

2/11/2000 4:02:19 PM

Hi evereyone, I would like to install 2 ASE servers with different versions on same linux box. Is this possible? Here is what I tried to do: I have a working ASE 11.9.2 on linux box tried to install ASE 11.0.3.3 on same box in different directory and it gave me an error. Wondering if this ispossible at all. Appreciate any help. TIA ar The 11.0.3.3 rpm tries to uninstall the 11.9.2 package. I shutdown the 11.9.2 server before install 11.0.3.3 and I do not keep the both versions running. The variables SYBASE, LD_LIBRARY_PATH ,PATH must be correctly set to point the...

Has any one successfully replicated from 11.0.3 using Rep Server 11.5 to ASE 11.9.2 using ltm's? We are running on solaris 2.6 using veritas filesystem and keep getting stack traces (this shuts down the connection). I would like to know if anyone else has replicated using these versions or something similar. Thanks. kboniface@worldres.com Yes, I have done this. No specific problems. HTH, Rob -- ---------------------------------------------------------------------- Rob Verschoor Certified Sybase ASE 11.5 Administrator (Professional) Certified DBA/Performance...

I am preparing to upgrade our production servers (11.0.3.3) using RepServer 11.5 with LTMs. My understanding is that the ASE 11.9.2 has RepAgents build in as Open Server applications. Are there any issues with upgrading that I need to be aware of? Where would I look? I would assume that I don't need to run LTMs anymore, but I need to ensure no loss of replication to the warm standby. Any advice? Take care. JCS ASE 11.5+ has RepAgent built in as internal server threads (which has nothing to do with OpenServer). When upgrading, keep the LTM's and upgrade to RS 11.5 / ...

Dear All: We have a Sybase ASE11.9.2 on Window NT, and we already run some application on it about 6 months. Since the third party software we use cann't support ASE11.9.2, we must use ASE 11.0.2. Can we just backup the database of ASE11.9.2 then restore the backup database to ASE11.0.2 ? Has it any problem ? Or, We must do following procedure. 1) bcp out the data from ASE11.9.2 2) create the database on ASE11.0.2 3) bcp in the 1) data to ASE11.0.2 Can anyone tell me?? Teresa Young Teresa, The problem with dump and load is that the log format in 11.9.2 is much diff...

I've seen different postings claiming that 11.0.3 can directly upgrade to 11.9.2. However, the release bulletin for Sybase ASE 11.9.2 (Doc ID = 72410-01-1192-02) says that "You can upgrade 11.9.2 only from 10.0.2.7, 11.0.3.3, 11.5 and later). We are currently using 11.0.3.2. Does it mean that I will have to upgrade our server to 11.0.3.3 (from 11.0.3.2) before I can migrate to ASE 11.9.2? I will appreciate if someone clarify this. Thanks. CGS tcgs@hotmail.com CGS wrote: > > I've seen different postings claiming that 11.0.3 can directly upgrade...

Hi, I was tryint to install SQL monitor server 11.9.2 on ASE 11.9.2 on HP-UX 11.0 9000/800. It gives the following error """""""""""""""""""""""" Nov 14 12:28:18 2000: DB-Library Version: Nov 14 12:28:18 2000: Sybase DB-Library/11.1.1/P-EBF8022/hp800/HP-UX 10.01/1/OPT/Fri Jun 26 15:04:39 1998 Nov 14 12:28:18 2000: Open Server Version: Nov 14 12:28:18 2000: Sybase Server-Library/11.1.1/P-EBF8022/hp800/HP-UX 10.01/1/OPT/Fri Jun 26 13:53:53 1998 Nov 14 12:2...

Hi, We are planning to upgrade our SQL Server 11.0.3.3 servers to ASE 11.9.2. We have 6 production servers on one UNIX box and (almost) corresponding test servers on another UNIX box. We have little new development, so we should have enough time to test things out first on the TEST platform. I am trying to decide between upgrading the servers in place or doing new installs and migrating the data over to the new servers. The upgrades in place would be a lot easier on me and the UNIX support people, but I am not sure how much more difficult fallback would be. If we needed to fall...

Hi I need to drop foreign key constraint and drop referenced table, then create another table-parent to be referenced . The problem is that it doesn't allow to drop table because of FKey constraint. When I try to drop the FKey constraint - it doesn't allow this and doesn't give an error message. This problem I experience on production server and this should be fixed urgently but seems like nothing I've tried helps to solve it. I'm using ASE 11.9.2 on NT4.0. If anybody has some ideas it would be very appreciated. Thank you. Ninel <<<< I need to drop ...

After migration from ASE 11.9.2 to ASE 12.5, the following error appears when I try to execute a strore procedure : Size of row in the worktable for grouped aggregate, 16542 bytes, exceeds the maximum row size (1962 bytes). Cannot run this query (return status = -6). Is it a bug on ASE 12.5? Why could I run correctly this procedure on the old version? Fabien DUBOIS Fabien DUBOIS wrote: > > After migration from ASE 11.9.2 to ASE 12.5, the following error appears > when I try to execute a strore procedure : > > Size of row in the worktable for grouped aggr...

Hi all I got the following problems using sql-remote. on ASE 12.5 eval: I got the following RPMs installed on RedHat Linux 7.2 - Kernel 2.4.17 - glibc-2.2.4 sybase-ase-12.5-3.i386.rpm sybase-common-12.5-3.i386.rpm sybase-openclient-12.5-3.i386.rpm sybase-sqlremote-7.0.2-1.i386.rpm After the execution of ssxtract or ssremote I got an error like: ../ssxtract: /opt/sybase-12.5/SQLRemote/lib/libsstasks7_r.so: version `libdbtasks7_r.so' not found (required by ./ssxtract) The file libsstasks7_r.so exists and the path is correctly specified in /etc/ld.so.conf. There'...

We are currently running version 11.9.2.3 on HP/UX 10.2. I would like to upgrade to 11.9.2.4 but another DBA has advised me that, if we do this, we will have to upgrade the client software on all the client machines. That sounds strange to me. We are currently running 11.1 clients on our PC's. Is anyone aware of a required client upgrade when going to 11.9.2.4? If so, are there any work arounds that would avoid having to upgrade all client PC's? Hi Robert. Moving from 11.9.2.3 to 11.9.2.4 should not impact your clients and would not require updating any software on th...

Hello, I need to transfer a database from a ASE 11.9.2 server to a ASE 11.5 server. (Servers have the same specs and run Windows NT 4.0). The database doesn't have many objects such as tables, users, Views, SP's etc. etc. It has a 400 MB device for data and 100 MB Device for the log, with standard segmentation. It's relatively small. I understand from this newsgroup that dumping on a newer release and loading on an older release is not an option. Now I figure I have to do the following: - Create a data and a log device with similar names and sizes on the ASE 11.5 se...

Hi, I have a customer that asks the following question: "Are there any issues running Sybase 32 and 64 bit applications on the same box? What about replication? Is it possible for us to upgrade to 12 repserver and dataserver on our central complex and continue to run 11.5 repserver/11.9.2 dataserver at remotes?" I would think that this configuration would be ok, but I thought I'd better verify it. Thanks, Pat Proft Alliance Support Pat Proft wrote: > Hi, > I have a customer that asks the following question:...

Hi, we have two database servers. one with Adaptive Server 11.9.2, the other with Adaptive Server 11.9.2.3. On both are exactly the same databases. In one table there is a char(1) column bound to a user datatype including a rule, which allows only a "Y" or a "N". The column is allowed to have NULL values If I try to insert a row under 11.9.2 nothing happens, I can insert a NULL value in this column. Under 11.9.2.3 I get the error 522 "A column insert or update conflicts with a rule bound to the column ..... " I cannot explain that for myself...

Ajahn Brahm (born 1951), Religious teacher and student of the late Thai monk Ajahn Chah . Currently he is the abbot of the Bodhinyana Monastery, ...

... reach to 98 percent of users who liked its page, as well as to double its engagement rate, during a 28-day period. Dr. Pepper hasnâ€™t yet converted ...

... Bowles, the former co-chair of the now-defunct National Commission on Fiscal Responsibility and Reform, and Reed Hastings, Netflix CEO, converted ...

... School of Business in Toronto on the topics of Sales and Entrepreneurship. I shared with them the sales process that I find How to Convert Leads ...

Answer (1 of 7): You should think of this question as "Should I invest all my savings in Bitcoin?", and not "Should I have my savings denominated ...

Get Coordinates - Calculate and Convert a Position on the App Store. See screenshots and ratings, and read customer reviews.

The man behind a radical plan to convert Domain Stadium into an artificial wave park, says it will become a sporting spectacle for wave-starved ...

Stunning wrestling diva Taryn Terrell has shared a dramatic knock-down testimony of conversion from life in the ring to life with Christ.

Microsoft today launched a new preview program for consumers who subscribe to Office 365 that will give them a sneak peak at new features slated ...

The Row-Bot was capable of generating more energy than it needed.

Resources last updated: 11/23/2015 6:48:21 AM