Solved

Round functino in oracle

Posted on 2011-09-21
8
274 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 76

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 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Author Comment

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

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

860 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