[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Round functino in oracle

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
Swadhin Ray
Asked:
Swadhin Ray
  • 5
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
I just noticed the scientific notation in the 'string'.  That appears to cause a problem.

Let me see what I can find.
0
 
slightwv (䄆 Netminder) Commented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Swadhin RaySenior Technical Engineer Author Commented:
@slightwv :
can you please explain me more on your comment ?
0
 
slightwv (䄆 Netminder) Commented:
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
 
Swadhin RaySenior Technical Engineer Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Swadhin RaySenior Technical Engineer Author Commented:
Thanks .. for detail explanation ...
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now