Hi Knut Thank you for your reply , but I still have some problems need your help to answer.
*Question one**: According to the reference manual: About divider,the scale expressions: 31 - lp + ls - rs the precision expressions: lp - ls + rp + max(ls + rp - rs + 1, 4) For example, the scale of the resulting data type of the following expression is 27: 11.0/1111.33 // 31 - 3 + 1 - 2 = 27 Another, the precision of the resulting data type of the following expression is 14: 11.0/1111.33 //3 - 1 + 6 + Max(1+6-2+1,4) = 14 Why the calculated results of the scale is less than the precision? I think that the precision (integer digits + scale digits) should be greater than the scale digits. *Question two**: In ij, I re-verify these statements and the results the same with you. But I use java code using a JDBC connection Derby ,it will appear. Can you help me explain the problem? -----邮件原件---- 发件人: Knut Anders Hatlen [mailto:knut.hat...@oracle.com] 发送时间: 2012年12月20日 18:07 收件人: 王旭 抄送: derby-user@db.apache.org 主题: Re: Some questions about the Derby Bryan Pendleton <bpendleton.de...@gmail.com> writes: > Forwarding to the list. > On 12/19/2012 04:10 AM, 王旭 wrote: >> *Question one**:*** >> >> Firstly, I created a table. >> >> CREATE TABLE CV >> >> ( >> >> AGE INTEGER NOT NULL, >> >> RT DECIMAL(20,10), >> >> PRIMARY KEY (AGE) >> >> ) >> >> Then innserted data "insert into CV VALUES (80,10729.5000000000)" >> >> Finally, an error occured when the SQL statement >> "select?(95000.0*1000/1000)/1000.0?*?RT?from?CV?where?Age=80?" was >> executed, it displayed "the generated value has gone beyond the data >> type of DECIMAL / NUMERIC (31,25)". >> >> Could you tell me how Derby do the precision control and operation on >> the data type of DECIMAL when it performs a variety of arithmetic >> operations including add, subtract, multiply and divide? The reference manual explains how numeric types are promoted in expressions here: http://db.apache.org/derby/docs/10.9/ref/rrefsqlj27767.html >> >> *Question two**:*** >> >> Firstly, I created a table. >> >> CREATE TABLE CV >> >> ( >> >> AGE SMALLINT, >> >> RT DOUBLE, >> >> PRIMARY KEY (AGE) >> >> ) >> >> Then innserted data "insert into CV VALUES (20,766.3)" >> >> Finally, I got results of "268204.99999999994" after executing the >> SQL statement "select 350000.0/1000 * RT from CV the where age = >> 20?", but the actual result should be "268205.0". When I execute those statements in ij, I see the result you expected: ij> CREATE TABLE CV (AGE SMALLINT, RT DOUBLE, PRIMARY KEY (AGE)); 0 rows inserted/updated/deleted ij> insert into CV VALUES (20, 766.3); 1 row inserted/updated/deleted ij> select 350000.0/1000 * RT from CV where age = 20; 1 ---------------------- 268205.0 1 row selected >> Could you tell me whether the cause of problem is inaccurated data >> from a floating-point type double. That sounds likely. But it's difficult to tell without knowing exactly what you did when you ended up with that number. -- Knut Anders