Solved

Round functino in oracle

Posted on 2011-09-21
8
268 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)
Comment Utility
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)
Comment Utility
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)
Comment Utility
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
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
@slightwv :
can you please explain me more on your comment ?
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

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…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now