Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Number Data type
Oracle Number Data type [message #316975] Tue, 29 April 2008 01:02 Go to next message
monash_aus
Messages: 2
Registered: April 2008
Junior Member
Hi,

I have a columns in the table with data type
col1 Number(18,6) &
col2 Numebr(12,4)

Basically I update the column col1 with col2/2 value. The values are storing properly in col1, but if col2 has a value like 0.0001, the value stored in col1 is 5E-5 instead of 0.00005. This is happening only when the value in col1 is 0.0001. If the value of col2 is 0.0005, then col1 is populated with proper data i.e., 0.00025.

Can anyone explain the reason behind this.

Re: Oracle Number Data type [message #316981 is a reply to message #316975] Tue, 29 April 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the value stored in col1 is 5E-5 instead of 0.00005.

No, the value is DISPLAYED like this not STORED like this.
It is just a display issue with your client program.

Regards
Michel
Re: Oracle Number Data type [message #316986 is a reply to message #316981] Tue, 29 April 2008 01:34 Go to previous messageGo to next message
monash_aus
Messages: 2
Registered: April 2008
Junior Member
Hi Michel,

I also first thought the same, but when the data is shown in the reporting tool(BO). The value is shown as 5E-5, the tool is not recognising it as a proper decimal number.
Re: Oracle Number Data type [message #316994 is a reply to message #316986] Tue, 29 April 2008 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a description of the table.
Use SQL*Plus and query the data.
Copy and paste this without forgetting to format it and use code tags as described in OraFAQ Forum Guide, "How to format your post?" section.

Regards
Michel
Re: Oracle Number Data type [message #317028 is a reply to message #316986] Tue, 29 April 2008 04:24 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that reporting tool (BO? What's that?) is responsible for that; or, should I say, DEVELOPER is the one to blame. If it is you, modify format mask so that it would show values the way you'd like them to.

Here's an SQL*Plus example:
SQL> create table test
  2  (col1 number(18, 6),
  3   col2 number(12, 4));

Table created.

SQL> insert into test (col2) values (0.0001);

1 row created.

SQL> update test set col1 = col2/2;

1 row updated.

SQL> select * from test;

      COL1       COL2
---------- ----------
    .00005      .0001

SQL> select 5e-5 from dual;

      5E-5
----------
    .00005

SQL>
Formatting means use of an appropriate mask, such as
SQL> select to_char(col1, '990D00000') c_1,
  2         to_char(col2, '990D00000') c_2
  3  from test;

C_1        C_2
---------- ----------
   0.00005    0.00010

SQL>
Re: Oracle Number Data type [message #317029 is a reply to message #317028] Tue, 29 April 2008 04:46 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
BO? What's that?

Business Objects, a pretty good BI toolset.
Re: Oracle Number Data type [message #317033 is a reply to message #317029] Tue, 29 April 2008 04:52 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you! I thought that it was Binary to Octal or even Boletim de Ocorrência, etc.
Previous Topic: Select from Dual
Next Topic: SQLTXPLAIN.SQL script
Goto Forum:
  


Current Time: Sat Dec 03 22:38:15 CST 2016

Total time taken to generate the page: 0.16815 seconds