Home » SQL & PL/SQL » SQL & PL/SQL » Ceil function
Ceil function [message #209551] Fri, 15 December 2006 08:51 Go to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
Hey guys I am trying to write a oracle special script that will round a figure to 2 significant figures

e.g. 2.15 will give 2.2

Can anybody help me out?!

I have
Select ceil(to_number(aavalue.valuestring)) from aavalue;

this will give out 3 but I need to figure out how to round a value to two significant figures

more e.g.
21.5 would give 22
0.215 would give .22

Hope ye can help guys its driving me mad
Re: Ceil function [message #209555 is a reply to message #209551] Fri, 15 December 2006 09:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In the words of Han Solo
Quote:
Sometimes I amaze even myself.

SQL> with mytable as (select 12345.678  col_1 from dual union all
  2                   select 1234.5678  col_1 from dual union all
  3                   select 123.45678  col_1 from dual union all
  4                   select 12.345678  col_1 from dual union all
  5                   select 1.2345678  col_1 from dual union all
  6                   select .12345678  col_1 from dual union all
  7                   select .012345678  col_1 from dual union all
  8                   select .0012345678  col_1 from dual)
  9  select col_1, round(col_1,-1*(floor(log(10,col_1))-1)) from mytable;

     COL_1 ROUND(COL_1,-1*(FLOOR(LOG(10,COL_1))-1))
---------- ----------------------------------------
 12345.678                                    12000
 1234.5678                                     1200
 123.45678                                      120
 12.345678                                       12
 1.2345678                                      1.2
 .12345678                                      .12
.012345678                                     .012
.001234568                                    .0012
Re: Ceil function [message #209556 is a reply to message #209555] Fri, 15 December 2006 09:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just to prove that it does the rounding properly:
SQL> with mytable as (select 12345.678  col_1 from dual union all
  2                   select 12545.678  col_1 from dual union all
  3                   select 123.45678  col_1 from dual union all
  4                   select 125.45678  col_1 from dual union all
  5                   select 12.345678  col_1 from dual union all
  6                   select 12.545678  col_1 from dual union all
  7                   select 1.2345678  col_1 from dual union all
  8                   select 1.2545678  col_1 from dual union all          
  9                   select .12345678  col_1 from dual union all
 10                   select .12545678  col_1 from dual)
 11  select col_1, round(col_1,-1*(floor(log(10,col_1))-1)) from mytable;

     COL_1 ROUND(COL_1,-1*(FLOOR(LOG(10,COL_1))-1))
---------- ----------------------------------------
 12345.678                                    12000
 12545.678                                    13000
 123.45678                                      120
 125.45678                                      130
 12.345678                                       12
 12.545678                                       13
 1.2345678                                      1.2
 1.2545678                                      1.3
 .12345678                                      .12
 .12545678                                      .13

10 rows selected.


Out of curiosity - what is an 'Oracle Special Script' ?
Re: Ceil function [message #209852 is a reply to message #209556] Mon, 18 December 2006 02:39 Go to previous messageGo to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
Thats what we call oracle sql scripts.

Sorry not the best at oracle scripts I cant figure out what you are doing.

My script so far is:
Select ceil(to_number(2.15)) from table

when I input that the result I get is 3.

I need to write a script that will round a figure to two significant figures.

e.g.
2.15 should be 2.2
21.5 should give 22
0.215 should give .22

I hope ye can help I dont have a clue of sql script and am really struggling.

Thanks in advance
Re: Ceil function [message #209859 is a reply to message #209852] Mon, 18 December 2006 03:39 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
JRowbottom created sample case (because you didn't do that). Instead of typing separate SELECT statements, he put it all into one.

In other words, and using his solution, you'd have to do this:
SQL> select 2.15, round(2.15,-1*(floor(log(10,2.15))-1)) res from dual;

      2.15        RES
---------- ----------
      2.15        2.2

SQL> select 21.5, round(21.5,-1*(floor(log(10,21.5))-1)) res from dual;

      21.5        RES
---------- ----------
      21.5         22

SQL> select 0.215, round(0.215,-1*(floor(log(10,0.215))-1)) res from dual;

     0.215        RES
---------- ----------
      .215        .22
Re: Ceil function [message #209860 is a reply to message #209556] Mon, 18 December 2006 03:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
the phrase
WITH mytable AS (...)
simply allows me to use 'mytable' as a shorthand for the code inside the brackets. In this case, 'mytable' is a set of unioned queries that returns the sample data that we need for the demo of the solution.

The functional part of the solution is
round(col_1,-1*(floor(log(10,col_1))-1)) 

I use the log (base 10) to work out where the first significant figure in the number is, then use Floor to get rid of the decimal part. There's a fortunate coincedence here - for numbers < 1, floor drops the number to 1 lower than the position of the first significant digit, but this neatly compensates for the fact that I've now got a '.' in the number that I need to take care of.

Re: Ceil function [message #209865 is a reply to message #209860] Mon, 18 December 2006 03:52 Go to previous messageGo to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
Thank you very much for explaining it fully I know you had to go out of your way to explain it.

Thank you and thank you for your help, you've really helped me out of a hole.

Re: Ceil function [message #209867 is a reply to message #209551] Mon, 18 December 2006 03:56 Go to previous messageGo to next message
venurajkumar
Messages: 4
Registered: December 2006
Location: chennai
Junior Member
dear friend,
as per your
example
2.15 is to be rounded to 2.2
you can try this query
select round(2.15,1)from dual;
Re: Ceil function [message #209873 is a reply to message #209865] Mon, 18 December 2006 04:16 Go to previous messageGo to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
I'm sorry to be annoying ye all but what do I need to do to fix the ORA-00932 error

ORA-00932 expected number got CLOB

[Updated on: Mon, 18 December 2006 04:33]

Report message to a moderator

Re: Ceil function [message #209880 is a reply to message #209873] Mon, 18 December 2006 04:40 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
ORA-00932 inconsistent datatypes

Cause: One of the following:

An attempt was made to perform an operation on incompatible datatypes. For example, adding a character field to a date field (dates may only be added to numeric fields) or concatenating a character field with a long field.

An attempt was made to perform an operation on a database object (such as a table or view) that is not intended for normal use. For example, system tables cannot be modified by a user. Note that on rare occasions this error occurs because a misspelled object name matched a restricted object's name.

An attempt was made to use an undocumented view.

Action: If the cause is

different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.

an object not intended for normal use, then do not access the restricted object
Re: Ceil function [message #209883 is a reply to message #209880] Mon, 18 December 2006 04:52 Go to previous messageGo to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
I googled it and got that explaination.

I tried to modify it to work on the script

e.g.
Select aavalue.valuestring, round(aavalue.valuestring,-1*(floor(log(10,aavalue.valuestring))-1)) from aavalue;

The valuestring character is set up as a CLOB and is needed as a CLOB so I have been told so when I try

Select to_number(aavalue.valuestring), round(aavaluestring,-1*(floor(log(10,aavalue.valuestring))-1)) from aavalue;

I still get the error.


Re: Ceil function [message #209885 is a reply to message #209880] Mon, 18 December 2006 04:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd guess that the datatype of the column that you're trying to convert the contents of is a CLOB, not a NUMBER.

You might be able to work round this by replacing 'COL_1' in my example with 'TO_NUMBER(COL_1)'

@venurajkumar - I have to ask: Why do you think I would create a solution as baroque as the one I posted if the problem could be solved with a single ROUND statment. Did it never occur to you that you might be overlooking something?
Re: Ceil function [message #209888 is a reply to message #209885] Mon, 18 December 2006 05:05 Go to previous messageGo to next message
Anto318
Messages: 27
Registered: December 2006
Junior Member
Thank you very much JRowbottom

I really appreciate your help and your quick responses.

To get the lowest value is there much work to modify the code you gave me to do it?

e.g.
0.0215 gives 0.021
0.215 gives 0.21
21.5 gives 21

Re: Ceil function [message #209903 is a reply to message #209888] Mon, 18 December 2006 06:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can just replace ROUND with TRUNC:
trunc(col_1,-1*(floor(log(10,col_1))-1))
Re: Ceil function [message #209905 is a reply to message #209903] Mon, 18 December 2006 06:18 Go to previous message
Anto318
Messages: 27
Registered: December 2006
Junior Member
Thank you very much works like a treat
Previous Topic: Undo
Next Topic: Calculating date difference when inserting..
Goto Forum:
  


Current Time: Fri Dec 02 20:31:23 CST 2016

Total time taken to generate the page: 0.28311 seconds