Precision

palkodi

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)

mathguy

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).

User_W58DO

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

John Thorton

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

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

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).

Unknown

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?

palkodi

"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

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

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

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.

palkodi

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