rounding problem with Base

users
#1

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

#2

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

#3

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.

#4

Hi :slight_smile:
Are you saying you have solved this problem yourself without help from
the list or has the problem just changed a bit?
Regards from
Tom :slight_smile:

#5

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?

#6

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.

#7

Hi :slight_smile:
You can make a "Feature Request" usignt he bug-report system
https://wiki.documentfoundation.org/QA/BugReport
Just hunt around the drop-downs to find the one that has "Feature
request" at the bottom of it's list. At least i think it's at the
bottom, they might have moved it up in the last couple of years.
Regards from
Tom :slight_smile: