Number Datatype Format [message #9324] |
Tue, 04 November 2003 10:24 |
Muhammad Waqas Shabir
Messages: 26 Registered: April 2003
|
Junior Member |
|
|
I am storing e.g 0.56 in a number data type column but when i retrive that value. It shows 1.
To my understanding oracle is rounding my numbers. I dont want this to happen as this figure is of currency e.g i want to store o.234 million. how would i be able to do it.
How will i be performing calculations precisely?
Thanking u in anticipation.
Waqas
|
|
|
Re: Number Datatype Format [message #9325 is a reply to message #9324] |
Tue, 04 November 2003 10:43 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
Is the number defined on your table as something like NUMBER(10)? What are the results of DESCRIBE my_table?
What do you get when you issue SELECT * FROM table WHERE my_column = 0.56?
What are the results when you issue SHOW NUMFORMAT and SHOW NUMWIDTH in SQL*Plus?
Do you have any formatting on the column?SQL> CREATE TABLE t (n NUMBER);
Table created.
SQL> INSERT INTO t VALUES (0.56);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> COLUMN n FORMAT 9999
SQL> SELECT n
2 FROM t
3 /
N
-----
1
SQL> CLEAR COLUMN
columns cleared
SQL> SELECT n
2 FROM t
3 /
N
----------
.56
SQL> HTH,
A.
|
|
|
Re: Number Datatype Format [message #9345 is a reply to message #9325] |
Wed, 05 November 2003 07:58 |
Muhammad Waqas Shabir
Messages: 26 Registered: April 2003
|
Junior Member |
|
|
I have the created a column like this create table ABC( P_DisbN25 NUMBER(25));
In above statement, i have tried to store a 25 digits number in my column "P_DisbN25". Am i doing the right thing for storing currency values to it?
I am not applying any format neither at insertion nor at retrieval.
So, R u getting my point now?
Do explain/recommend any database design changes or query format changes with examples.
Thanks
Waqas
|
|
|
Re: Number Datatype Format [message #9346 is a reply to message #9345] |
Wed, 05 November 2003 08:28 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
You should read the documentation on the NUMBER datatype:
----------------------------------------------------------------------
[size=3][font=Arial, Helvetica, sans-serif]NUMBER Datatype[/font][/size]
The [i]NUMBER[/i] datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10[sup]-130[/sup] and 9.9...9 x 10[sup]125[/sup] (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10[sup]126[/sup], then Oracle returns an error.
Specify a fixed-point number using the following form:
[code]NUMBER(p,s) [/code]
where:
[list][*][i][i]p[/i][/i] is the [b]precision[/b], or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.[*][i][i]s[/i][/i] is the [b]scale[/b], or the number of digits to the right of the decimal point. The scale can range from -84 to 127.[/list]
Specify an integer using the following form:
[code]NUMBER(p)[/code]
This represents a fixed-point number with precision p and scale 0 and is equivalent to [i]NUMBER(p,0)[/i].
Specify a floating-point number using the following form:
[code]NUMBER[/code]
The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.
----------------------------------------------------------------------
By setting up your column as a NUMBER(25), you are saying that you wish to store only integer values in this column.
Why not just define your column as a plain old NUMBER and be done with it? (I.e., CREATE TABLE abc (p_disbn25 NUMBER);).)
A.
|
|
|