Solved

Round functino in oracle

Posted on 2011-09-21
8
275 Views
Last Modified: 2012-05-12
Hello experts,

I have created a table like below:

SQL> create table gunti (col1 number) ;


And inserted one record as below:

SQL> insert into gunti values('-2.1005799412757564E16') ;
SQL> commit;

I want to round the value like -2.10
0
Comment
Question by:Swadhin Ray
  • 5
  • 3
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36573508
How about the round function?

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions155.htm#SQLRF00698

select round(col1,2) from gunti;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36573528
I just noticed the scientific notation in the 'string'.  That appears to cause a problem.

Let me see what I can find.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36573632
Now that I've played with this a little, I'm not following the question.

-2.1005799412757564E16 equates to -21005799412757564

How do you expect to get -2.10?
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36574424
@slightwv :
can you please explain me more on your comment ?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36574442
Which comment?  I made three.

This one:  -2.1005799412757564E16 equates to -21005799412757564

2.10E16 is scientific notation.  It is a way to show large numbers in a condensed format.  It is not a  number with decimal places.

http://en.wikipedia.org/wiki/Scientific_notation
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 36575801
Can you tell me the calculation for conversion based on my input?

How to find the exact conversion numbers for such input like mine.
As far as looked into the logic the notation should be on bases of 10.

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36575895
I'm not understanding what you want/need to do with the number.

To display the number in it's complete form in sqlplus just use: set numwidth 20

Below is an example
SQL> drop table gunti purge;

Table dropped.

SQL> create table gunti (col1 number) ;

Table created.

SQL> insert into gunti values('-2.1005799412757564E16') ;

1 row created.

SQL> commit;

Commit complete.

SQL> set numwidth 20
SQL> select col1 from gunti;

                COL1
--------------------
  -21005799412757564

SQL>

Open in new window

0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 36582898
Thanks .. for detail explanation ...
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question