Home » SQL & PL/SQL » SQL & PL/SQL » sql help( how can use floor and ceil)
sql help( how can use floor and ceil) [message #227883] Fri, 30 March 2007 00:41 Go to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i have table like

create table rama3 as
(select 1 num from dual
union
select -2.1 num from dual
union
select -2 num from dual
union
select 1.1 num from dual)

reqired
num
1
-3
-2
2

Re: sql help( how can use floor and ceil) [message #227886 is a reply to message #227883] Fri, 30 March 2007 00:59 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
yes

may be

select case when sign(num)= -1 then floor(num) else when sign(num) =1 then ceil(num) end from rama3

Re: sql help( how can use floor and ceil) [message #227887 is a reply to message #227886] Fri, 30 March 2007 01:01 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry

i got this
see this



select case when sign(num)= -1 then floor(num) else ceil(num) end from rama3
Re: sql help( how can use floor and ceil) [message #227889 is a reply to message #227887] Fri, 30 March 2007 01:08 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
SQL> desc t1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GETAL                                              FLOAT(126)

SQL> select getal, case when getal<0 then floor(getal) else ceil(getal) end from t1;

     GETAL CASEWHENGETAL<0THENFLOOR(GETAL)ELSECEIL(GETAL)END
---------- -------------------------------------------------
      -1.1                                                -2
        -1                                                -1
       -.9                                                -1
        .9                                                 1
         1                                                 1
       1.1                                                 2

6 rows selected.

SQL>



maybe this is what you are looking for
Re: sql help( how can use floor and ceil) [message #227898 is a reply to message #227889] Fri, 30 March 2007 02:00 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Instead of using a comlexer case construction you can use SIGN, CEIL and ABS:
SQL> WITH yourtable AS
  2   (
  3     SELECT 1    num,  1 desired FROM dual UNION ALL
  4     SELECT -2.1 num, -3 desired FROM dual UNION ALL
  5     SELECT -2   num, -2 desired FROM dual UNION ALL
  6     SELECT 1.1  num,  2 desired FROM dual
  7   )
  8  SELECT num
  9       , desired
 10       , sign(num)*ceil(abs(num)) rounded
 11  FROM yourtable
 12  /

       NUM    DESIRED    ROUNDED
---------- ---------- ----------
         1          1          1
      -2.1         -3         -3
        -2         -2         -2
       1.1          2          2


Look up the definitions in the SQL Reference.

MHE

[Updated on: Fri, 30 March 2007 02:00]

Report message to a moderator

Re: sql help( how can use floor and ceil) [message #227905 is a reply to message #227898] Fri, 30 March 2007 02:25 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
oooo

That is much better (less complex) indeed.

thanks
Re: sql help( how can use floor and ceil) [message #227908 is a reply to message #227905] Fri, 30 March 2007 02:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think the case version is far more readable. At first glance it is clear what is done.
The sign/abs thing, although a nice trick, is less intuitive.
But, then again, this is my humble opinion.
Re: sql help( how can use floor and ceil) [message #227911 is a reply to message #227908] Fri, 30 March 2007 02:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Shut up! Very Happy Mine is better, that goes without saying*.

MHE

*note the tongue in cheek. Martijn's solution is a nice example how a similar result can be achieved following a different logic.

[Updated on: Sat, 31 March 2007 06:51]

Report message to a moderator

Re: sql help( how can use floor and ceil) [message #228070 is a reply to message #227911] Sat, 31 March 2007 03:30 Go to previous message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
see the flowing link

http://www.techonthenet.com/oracle/functions/ceil.php

http://www.techonthenet.com/oracle/functions/floor.php
Previous Topic: about indexes
Next Topic: Selecting from a varray (Merged)
Goto Forum:
  


Current Time: Sat Dec 10 13:11:19 CST 2016

Total time taken to generate the page: 0.15442 seconds