Forum Stats

  • 3,781,589 Users
  • 2,254,530 Discussions
  • 7,879,762 Comments

Discussions

Precision

palkodi
palkodi Member Posts: 88
edited May 11, 2018 5:52PM in SQL & PL/SQL

I am trying to learn more about precision can some one explain me in a plain english ...

I am table column with number(22) and another table with column with number(15,2)

if I reduce column data type from number(22) to number(15,2)

I have alter table schema.column add (new_table number(15,2))

update schema.column set new_table = table

alter table schema.column drop column table

alter table schema.column rename  new_table to table;

will this make any difference from number(22) to number(15,2)

Tagged:
John Thortonpalkodi

Best Answer

  • mathguy
    mathguy Member Posts: 10,221 Blue Diamond
    edited May 11, 2018 3:44PM Accepted Answer

    If you are looking to make the tables compatible and not lose any information, you will need a precision and scale that can accommodate 22 digits in the integer part, as well as two decimal places. To do that you will need a total precision of 24. The minimal precision/scale combination that allows you to preserve all the information is therefore:

    NUMBER(24,2)

    (24 is for 22 digits in the integer part, PLUS two more digits for the decimal part).

    palkodipalkodi
«1

Answers

  • User_W58DO
    User_W58DO Member Posts: 151 Red Ribbon
    edited May 11, 2018 3:33PM

    NUMBER (precision, scale)

    If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

    palkodipalkodi
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 11, 2018 3:34PM
    palkodi wrote:I am trying to learn more about precision can some one explain me in a plain english ... I am table column with number(22) and another table with column with number(15,2)if I reduce column data type from number(22) to number(15,2)I have alter table schema.column add (new_table number(15,2))update schema.column set new_table = tablealter table schema.column drop column tablealter table schema.column rename new_table to table;will this make any difference from number(22) to number(15,2)

    not all values that are OK in NUMBER(22) can be held in NUMBER(15,2)

    SQL> CREATE TABLE NUMBERS (

      2  NUM1 NUMBER(22),

      3  NUM2 NUMBER(15,2));

    Table created.

    SQL> INSERT INTO NUMBERS values (123456789012345678901,123456789012.34);

    1 row created.

    SQL> UPDATE NUMBERS SET NUM2=NUM1;

    UPDATE NUMBERS SET NUM2=NUM1

                            *

    ERROR at line 1:

    ORA-01438: value larger than specified precision allowed for this column

    SQL> SELECT * FROM NUMBERS;

          NUM1 NUM2

    ---------- ----------

    1.2346E+20 1.2346E+11

  • palkodi
    palkodi Member Posts: 88
    edited May 11, 2018 3:38PM

    if the source table has the number(15,2) and target table has number(22) do u think its better to decrease the precision or increase the precesion in target table instead of number(15,2) to number(22,2) will this make any sense to it?

  • mathguy
    mathguy Member Posts: 10,221 Blue Diamond
    edited May 11, 2018 3:44PM Accepted Answer

    If you are looking to make the tables compatible and not lose any information, you will need a precision and scale that can accommodate 22 digits in the integer part, as well as two decimal places. To do that you will need a total precision of 24. The minimal precision/scale combination that allows you to preserve all the information is therefore:

    NUMBER(24,2)

    (24 is for 22 digits in the integer part, PLUS two more digits for the decimal part).

    palkodipalkodi
  • Unknown
    edited May 11, 2018 4:03PM
    if the source table has the number(15,2) and target table has number(22) do u think its better to decrease the precision or increase the precesion in target table instead of number(15,2) to number(22,2) will this make any sense to it?

    Please make up your mind what the REAL requirements are and then post them.

    If your first post you said you were going to put the data from a 'number(22)' column into a 'number(165,2)' column.

    Now you say just the OPPOSITE.

    Which is it.

    Only YOU know:

    1. where your data comes from - all sources of the data for the table (that includes backups that might get restored)

    2. the precision of that data

    3. whether the original precision and scale needs to be maintained or not

    Until we know ALL of the requirements we can't advise you.

    In some circumstances Oracle will ROUND data that you put into a column. That means you LOSE PRECISION FOREVER. Is that ok?

    John Thorton
  • palkodi
    palkodi Member Posts: 88
    edited May 11, 2018 4:12PM

    "If your first post you said you were going to put the data from a 'number(22)' column into a 'number(165,2)' column."

    I never said number(165,2) in any of my post.

    well i asked for the suggestion if I can reduce number(15,2)

    well source table I get is number(15,2)

    ALL Target table are number (22) .. So I was asking if there would be any problem if I reduce? please correct if I am wrong . please appologise my english. Thank you

  • mathguy
    mathguy Member Posts: 10,221 Blue Diamond
    edited May 11, 2018 4:32PM

    You can try to "reduce" from NUMBER(22) to NUMBER(15,2). You will have room for two decimal places; that will go unused, because NUMBER(22) - which is shorthand for NUMBER(22,0) - can only store numbers with 0 decimal places (they are INTEGERS).

    I say you can "try" - you may or may not succeed. NUMBER(15,2) reserves 2 places for decimals, so there are only 13 digits in the integer part. If your NUMBER(22) precision was "unnecessarily large" to begin with - if you are certain (or you can check with a SELECT statement) that all the numbers in the NUMBER(22) column really only have <= 13 digits, then you will succeed. But you may fail, as John showed earlier, if some of the numbers in NUMBER(22) have more than 13 digits.

    It is safer to go the other way around - from NUMBER(15,2) to NUMBER(22). That will always succeed, but you may lose information - numbers that are not integers will be rounded to the nearest integer value. In the U.S., for example, when you calculate your federal taxes, you are REQUIRED to round all amounts to the nearest integer, so in that context, rounding would be OK. In many other contexts, it is NOT OK to lose information.

    The safest is, as I suggested already, to convert both columns to NUMBER(24,2) - not to try to convert one of the columns to the other one's type/precision/scale.

    palkodi
  • palkodi
    palkodi Member Posts: 88
    edited May 11, 2018 4:42PM

    can u clarify me one more doubt if my source which I am getting is number(15,2) and target or output table is number(22) then can I only convert number(22) =Number(24,2) keep the source table number(15,2) as it is..... will that loose data ..source table is giving data to other table so will that loose information?

  • mathguy
    mathguy Member Posts: 10,221 Blue Diamond
    edited May 11, 2018 4:53PM

    No, you will not lose data in that scenario. So, to be clear: you have a SOURCE table (or other data source) and a TARGET table. Data flows only from the source to the target, never in the opposite direction. Then, if you change the TARGET column from NUMBER(22) to NUMBER(24,2)  then nothing that already exists in the TARGET table will be affected, and also, all numbers coming from NUMBER(15,2) will be stored in the TARGET without any loss of information.

    palkodipalkodi
  • palkodi
    palkodi Member Posts: 88
    edited May 11, 2018 4:56PM

    Yes that all I need Thank you for much patience and valuable information ... I am happy thank you 

This discussion has been closed.