Home » SQL & PL/SQL » SQL & PL/SQL » Any SQL function to reduce the size (Oracle 10g)
Any SQL function to reduce the size [message #404238] Thu, 21 May 2009 01:46 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

Is there any SQL function for the below scenario

1.My scenario is need to round off the value to the size (7,2)

for example1

the value is 12345689.2364,i need to round off to the size 7,2
(i.e.) 12345.23

for example2
if the value comes as 1000.56 then it should be left as it is

here the column's original size is (10,3) but i need to round off the value to 7,2

can any one help on this
Thanks in advance



Re: Any SQL function to reduce the size [message #404253 is a reply to message #404238] Thu, 21 May 2009 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the value is 12345689.2364,i need to round off to the size 7,2
(i.e.) 12345.23

This is not "round off". I think you will not accept someone telling you I will give you 12345689.2364 dollars that we will round off to 12345.23.
This is cutting arbitrary decimas places and has no mathematical definition (or function).
As it is custom you have to create your custom function (or use nested predefined ones) but you have to first clearly specify your operation.

Regards
Michel
Re: Any SQL function to reduce the size [message #404461 is a reply to message #404253] Thu, 21 May 2009 20:23 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
still, if all you want is to force a number to five on the left and two on the right as in (7,2) then use a combination of simple math, string functions and number functions.

The following does not do what you have described (I ain't doing your homework for you), but as you can see, it shows how to manipulate numbers as strings to change them to fit patterns etc.

substr(trunc(12345689.2364*100),-7)/100

SQL> select substr(trunc(12345689.2364*100),-7)/100
  2  from dual;

SUBSTR(TRUNC(12345689.2364*100),-7)/100
---------------------------------------
                               45689.23

SQL> 

You figure out what expression you need to build in order to get what ever it is you want. What will you do with negative numbers?

Good luck, Kevin

[Updated on: Thu, 21 May 2009 20:24]

Report message to a moderator

Previous Topic: Outer join creates multiple
Next Topic: Summary Materialized views
Goto Forum:
  


Current Time: Wed Dec 07 14:37:38 CST 2016

Total time taken to generate the page: 0.32865 seconds