[SOLVED + CAVEAT] [libreoffice-users] rounding problem with Base
On Fri, 27 Dec 2013 19:06:58 +0100 Ivan Sergio Borgonovo m...@webthatworks.it wrote: To make the change in format permanent you've to build a view from the query and change the format there otherwise if you change the format in the query the format is lost next time you run the query. A bit weird. If I could give a suggestion I'd make the default format for numbers in queries follow the same schema as in postgresql (and most DB I know). NUMERIC(A, N) * NUMERIC(B, M) - NUMERIC(A + B, N + M) and FLOAT/DOUBLE all significant digits. On Fri, 27 Dec 2013 15:57:47 + Tom Davies tomc...@gmail.com wrote: Hi :) Are you saying you have solved this problem yourself without help from the list or has the problem just changed a bit? To summarize it for posterity: I wasn't able to appreciate if any change I was doing to the type used (NUMERIC(N, M) or FLOAT or DOUBLE) in SQL operations was effective since all I could get out from the DB was 2 decimals either in simple format (1234567898,31) or scientific format (6,23E+015). Not being able to see any change tricked me into thinking that no matter what type I was using there was no effect on precision since I'm used to something like: test=# select 1.01::numeric(4,2)*2.02::numeric(4,2); ?column? -- 2.0402 (1 row) The problem was just a problem of output format that can be set once you run the query, and in the result window right click on the column name and change the format. Thanks for the moral support anyway. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[SOLVED] [libreoffice-users] rounding problem with Base
On Fri, 27 Dec 2013 01:30:27 +0100 Ivan Sergio Borgonovo m...@webthatworks.it wrote: It seems I just had to adjust the format of the column of the query once the query is run. Right click on the query result column and chose format. On Thu, 26 Dec 2013 21:41:48 +0100 Ivan Sergio Borgonovo m...@webthatworks.it wrote: I've a rounding or maybe display format problem with base. I'm making computations with NUMERIC(30, 2) I've a query (let's name it query1) that do some computation on numeric columns something like: SELECT SUM(c1 * c2 / c3) as s1 , SUM(c4 * c5 / c6) as s2 FROM table1 GROUP BY c0; This query actually returns several rows that have the precision I need. Then I've query as: SELECT SUM(s1 * s1) as ss1 from query1; SELECT SUM(s2) as ss2 from query1; And they return result in scientific notation with just 2 decimal visible 6,23E+015 When I try to do further computations with ss1 and ss2 I get 0 with NO decimal digits. When I import query1 in localc and do the sum and squaring and further computation there I can get the precision I need. I've been playing around with column definition, increasing decimal point in numerics and nothing changed. I couldn't see the number of decimals increase in any step of my computation. It seems related to: http://nabble.documentfoundation.org/rounding-off-number-results-in-Base-td4056773.html that's pretty an old problem and I couldn't find any workaround. This looks pretty critical I'm running: Version: 4.1.4.2 Build ID: 410m0(Build:2) in sid -- Ivan Sergio Borgonovo http://www.webthatworks.it -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] rounding problem with Base
I've a rounding or maybe display format problem with base. I'm making computations with NUMERIC(30, 2) I've a query (let's name it query1) that do some computation on numeric columns something like: SELECT SUM(c1 * c2 / c3) as s1 , SUM(c4 * c5 / c6) as s2 FROM table1 GROUP BY c0; This query actually returns several rows that have the precision I need. Then I've query as: SELECT SUM(s1 * s1) as ss1 from query1; SELECT SUM(s2) as ss2 from query1; And they return result in scientific notation with just 2 decimal visible 6,23E+015 When I try to do further computations with ss1 and ss2 I get 0 with NO decimal digits. When I import query1 in localc and do the sum and squaring and further computation there I can get the precision I need. Thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] rounding problem with Base
On Thu, 26 Dec 2013 21:41:48 +0100 Ivan Sergio Borgonovo m...@webthatworks.it wrote: I've a rounding or maybe display format problem with base. I'm making computations with NUMERIC(30, 2) I've a query (let's name it query1) that do some computation on numeric columns something like: SELECT SUM(c1 * c2 / c3) as s1 , SUM(c4 * c5 / c6) as s2 FROM table1 GROUP BY c0; This query actually returns several rows that have the precision I need. Then I've query as: SELECT SUM(s1 * s1) as ss1 from query1; SELECT SUM(s2) as ss2 from query1; And they return result in scientific notation with just 2 decimal visible 6,23E+015 When I try to do further computations with ss1 and ss2 I get 0 with NO decimal digits. When I import query1 in localc and do the sum and squaring and further computation there I can get the precision I need. I've been playing around with column definition, increasing decimal point in numerics and nothing changed. I couldn't see the number of decimals increase in any step of my computation. It seems related to: http://nabble.documentfoundation.org/rounding-off-number-results-in-Base-td4056773.html that's pretty an old problem and I couldn't find any workaround. This looks pretty critical I'm running: Version: 4.1.4.2 Build ID: 410m0(Build:2) in sid -- Ivan Sergio Borgonovo http://www.webthatworks.it -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted