I want to prevent a number from rounding in Oracle Database I want to prevent a number from rounding in Oracle Database I want to prevent a number from rounding in Oracle Database I want to prevent a number from rounding in Oracle Database price = 14.089 => becomes : 14.09

In my data base the type of the Column is DECIMAL

I want to get the 3 numbers of the price after the comma. can anyone hel me how to fix this problem

SELECT
    *
FROM
    user_tab_columns
WHERE
    table_name = 'GEST_SANTE.PRESTATION_PHARMACIE'
AND column_name = 'PRICE';

The result after executing the query:

INSERT INTO ""."" (

    "OWNER",
    "TABLE_NAME",
    "COLUMN_NAME",
    "DATA_TYPE",
    "DATA_TYPE_MOD",
    "DATA_TYPE_OWNER",
    "DATA_LENGTH",
    "DATA_PRECISION",
    "DATA_SCALE",
    "NULLABLE",
    "COLUMN_ID",
    "DEFAULT_LENGTH",
    "DATA_DEFAULT",
    "NUM_DISTINCT",
    "LOW_VALUE",
    "HIGH_VALUE",
    "DENSITY",
    "NUM_NULLS",
    "NUM_BUCKETS",
    "LAST_ANALYZED",
    "SAMPLE_SIZE",
    "CHARACTER_SET_NAME",
    "CHAR_COL_DECL_LENGTH",
    "GLOBAL_STATS",
    "USER_STATS",
    "AVG_COL_LEN",
    "CHAR_LENGTH",
    "CHAR_USED",
    "V80_FMT_IMAGE",
    "DATA_UPGRADED",
    "HISTOGRAM"
)
VALUES
    (
        'GEST_SANTE',
        'PRESTATION_PHARMACIE',
        'PRICE',
        'NUMBER',
        NULL,
        NULL,
        '22',
        NULL,
        '3',
        'Y',
        '23',
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        'NO',
        'NO',
        NULL,
        '0',
        NULL,
        'NO',
        'YES',
        'NONE'
    );

CodePudding user response：

You have two issues and are conflating the two.

Issue 1: Data Storage

You are storing the data in a column with the DECIMAL(*,3) data type. This can store, at most, 3 decimal places. It is IMPOSSIBLE to store data with more than 3 decimal places and if you attempt it then Oracle will round the input to 3 decimal places because you defined the column as only having 3 decimal places and Oracle is doing exactly what you told it to do.

For example:

CREATE TABLE your_table_name (
  price DECIMAL(*,3)
);

Then:

INSERT INTO your_table_name (price) VALUES (14.0895);

It is IMPOSSIBLE to store the exact value as the column is only defined to store 3 decimal places so Oracle will round the number to 14.090 and store that value. Since 14.090 and 14.09 are identical values the database only needs to store 14.09 and the third decimal place is irrelevant as it is 0 and does not need to be stored (and the value is unaffected).

Yes, rounding has occurred but it is because YOU defined the column to only store 3 decimal places and tried to insert a number with 4 decimal places so Oracle does its best to store the closest value it can (given the constraints that YOU set on the table) and that is through rounding to the appropriate scale.

---

If you want to change the scale of a column so that you can store a greater number of decimal places then you can do that using:

ALTER TABLE your_table_name MODIFY (price DECIMAL(*,5));

However, it will not change the data already stored in the column; but it would allow you to insert more precise values at a later date.

fiddle

---

Issue 2: Displaying Data

Oracle does NOT store data with any particular formatting. If you want to display a value in the database as a number then Oracle will only display the stored digits; it will not automatically format the number to the maximum scale of the column.

For example:

SELECT price FROM your_table_name;

Will output:

PRICE
14.09

If you want to display it with any particular formatting then YOU need to convert the number to a formatted string in the SELECT statement and provide the format model to use in the formatting. So to always display 3 decimal places:

SELECT TO_CHAR(price, 'fm999990.000') AS price
FROM   your_table_name;

Will output:

PRICE
14.090

fiddle

CodePudding user response：

You can use this :

FLOOR(price * 1000)/1000;