Home » SQL & PL/SQL » SQL & PL/SQL » Next multiple of 10 value for the given integer (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Next multiple of 10 value for the given integer [message #595314] Tue, 10 September 2013 02:35 Go to next message
saipradyumn
Messages: 182
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All ,

Is there any function which return the next multiples of 10 value directly ?

Ex : input - 11 then it should return 20
156 - 160
299 - 300
43 - 50

Thanks
Saipradyumn
Re: Next multiple of 10 value for the given integer [message #595315 is a reply to message #595314] Tue, 10 September 2013 02:39 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
Divide the number by 10, round it up (ceil function will do that) Multiply the result of that by 10
Re: Next multiple of 10 value for the given integer [message #595316 is a reply to message #595314] Tue, 10 September 2013 02:41 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1850
Registered: May 2013
Location: World Wide on the Web
Senior Member
saipradyumn wrote on Tue, 10 September 2013 13:05

Is there any function which return the next multiples of 10 value directly ?


No such inbuilt function, however, you can write a SQL to achieve your task. Should be quite simple.
Re: Next multiple of 10 value for the given integer [message #595317 is a reply to message #595314] Tue, 10 September 2013 02:42 Go to previous messageGo to next message
Maaher
Messages: 7041
Registered: December 2001
Senior Member
You can use CEIL, which rounds up to the next integer. If you divide your value by ten, apply ceil and multiply again, you get your desired output.

SQL> With mydata As
  2  ( Select  11 thevalue from dual union all
  3    Select 156          from dual union all
  4    Select 199          from dual union all
  5    Select  44          from dual
  6  )
  7  Select thevalue
  8       , ceil(thevalue/10)*10 roundup
  9  From   mydata
 10  /

  THEVALUE    ROUNDUP
---------- ----------
        11         20
       156        160
       199        200
        44         50


MHE
Re: Next multiple of 10 value for the given integer [message #595321 is a reply to message #595317] Tue, 10 September 2013 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please let OP a chance to learn.

Tell me and I'll forget; show me and I may remember; involve me and I'll understand

Regards
Michel
Re: Next multiple of 10 value for the given integer [message #595326 is a reply to message #595321] Tue, 10 September 2013 03:13 Go to previous messageGo to next message
Maaher
Messages: 7041
Registered: December 2001
Senior Member
Michel, I'm puzzled. What can you teach him by not replying? How can you involve the OP without posting a reply?

MHE

[Updated on: Tue, 10 September 2013 03:13]

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595332 is a reply to message #595326] Tue, 10 September 2013 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant you could give her only the first sentence without spoonfeeding her with the code.

(In addition, with more than 300 posts and 2 years here she would know she have to post a test case with her question.)

Regards
Michel
Re: Next multiple of 10 value for the given integer [message #595354 is a reply to message #595332] Tue, 10 September 2013 09:48 Go to previous messageGo to next message
Bill B
Messages: 1068
Registered: December 2004
Senior Member
just to show that theirs always more ways to skin a cat in oracle.


1* select round(241,-1) + 10 from dual;

ROUND(241,-1)+10
----------------
250
Re: Next multiple of 10 value for the given integer [message #595362 is a reply to message #595354] Tue, 10 September 2013 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Close but not exactly:
SQL> select round(245,-1) + 10 from dual;
ROUND(245,-1)+10
----------------
             260

Regards
Michel
Re: Next multiple of 10 value for the given integer [message #595365 is a reply to message #595354] Tue, 10 September 2013 12:01 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
Another way to skin the cat (what a horrible phrase that is):
orclz>
orclz> select 241 +10 - mod((241+10),10) from dual;

241+10-MOD((241+10),10)
-----------------------
                    250

orclz>


--update, jw

or better,
orclz> select 241 + 10 - mod(241,10) from dual;

241+10-MOD(241,10)
------------------
               250

orclz>

[Updated on: Tue, 10 September 2013 12:09]

Report message to a moderator

Re: Next multiple of 10 value for the given integer [message #595367 is a reply to message #595365] Tue, 10 September 2013 12:15 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The cats keep coming (or are we just beating a dead horse)?? Too many animal references!

Anyway:
SQL> With mydata As
  2  ( Select  11 thevalue from dual union all
  3    Select 156          from dual union all
  4    Select 199          from dual union all
  5    Select  44          from dual
  6  )
  7  Select thevalue
  8       , trunc(thevalue,-1)+10 roundup
  9  From   mydata
 10  /

  THEVALUE    ROUNDUP
---------- ----------
        11         20
       156        160
       199        200
        44         50

Thanks for the code Maaher!
Re: Next multiple of 10 value for the given integer [message #595368 is a reply to message #595367] Tue, 10 September 2013 12:37 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
And here's my latest attempt to boil the bunny:
orclz> ed
Wrote file afiedt.buf

  1  With mydata As
  2      ( Select  11 thevalue from dual union all
  3        Select 156          from dual union all
  4        Select 199          from dual union all
  5        Select  44          from dual
  6      )
  7      Select thevalue
  8           , to_number(substr(to_char(thevalue),1,length(to_char(thevalue)) - 1)||'0')+10
  9*     From   mydata
orclz> /

  THEVALUE TO_NUMBER(SUBSTR(TO_CHAR(THEVALUE),1,LENGTH(TO_CHAR(THEVALUE))-1)||'0')+10
---------- --------------------------------------------------------------------------
        11                                                                         20
       156                                                                        160
       199                                                                        200
        44                                                                         50

orclz>
Re: Next multiple of 10 value for the given integer [message #595395 is a reply to message #595368] Tue, 10 September 2013 18:34 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
There was actually a problem with my previous cat skinning when the number was either negative and/or a number already a multiple of 10, but I believe this one should work for all situations:

SQL> With mydata As
  2    ( Select 10 val from dual union all
  3      Select -21  from dual union all
  4      Select -35.2  from dual union all
  5      Select 156.1  from dual union all
  6      Select 199    from dual union all
  7      Select  44    from dual
  8    )
  9    Select val
 10         , decode(sign(val), '-1', -1*floor(abs(val)/10), ceil(val/10))*10 roundup
 11    From   mydata
 12  /

       VAL    ROUNDUP
---------- ----------
        10         10
       -21        -20
     -35.2        -30
     156.1        160
       199        200
        44         50

6 rows selected.


Re: Next multiple of 10 value for the given integer [message #595411 is a reply to message #595395] Wed, 11 September 2013 01:41 Go to previous messageGo to next message
Littlefoot
Messages: 19321
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
(Catskinners, you'd better not be seen by Barbara!)
Re: Next multiple of 10 value for the given integer [message #595413 is a reply to message #595395] Wed, 11 September 2013 01:55 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
Of all the solutions, I think the ebrian(modified) algorithm is the best in that it handles fractions and both positive and negative values. However, the handling of negatives requires further modification if the results are conform to the generally accepted standard of "round away from zero":
  1  With mydata As
  2        ( Select 10 val from dual union all
  3          Select -21  from dual union all
  4          Select -35.2  from dual union all
  5          Select 156.1  from dual union all
  6          Select 199    from dual union all
  7          Select  44    from dual
  8        )
  9        Select val
 10            , decode(sign(val), '-1', -1*ceil(abs(val)/10), ceil(val/10))*10 roundup
 11*      From   mydata
orclz> /

       VAL    ROUNDUP
---------- ----------
        10         10
       -21        -30
     -35.2        -40
     156.1        160
       199        200
        44         50

6 rows selected.

orclz>
This has to be the best solution. Though as Sai has not responded to any of this, I suppose she has lost interest. Or perhaps she found the references to domestic animals to be little off-putting.
Re: Next multiple of 10 value for the given integer [message #595575 is a reply to message #595413] Wed, 11 September 2013 16:49 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
John Watson wrote on Wed, 11 September 2013 02:55
...if the results are conform to the generally accepted standard of "round away from zero":

Yeah, I wasn't sure what protocol the OP was following for that.


John Watson wrote on Wed, 11 September 2013 02:55
Though as Sai has not responded to any of this, I suppose she has lost interest. Or perhaps she found the references to domestic animals to be little off-putting.

Littlefoot wrote on Wed, 11 September 2013 02:41
(Catskinners, you'd better not be seen by Barbara!)

Those are pretty good!
Re: Next multiple of 10 value for the given integer [message #595586 is a reply to message #595575] Thu, 12 September 2013 01:35 Go to previous message
saipradyumn
Messages: 182
Registered: October 2011
Location: Hyderabad
Senior Member

Thanks one & all for your valuable replies
Previous Topic: using regexp_replace to eliminate duplicate string with delimeter
Next Topic: insert select in PLSQL
Goto Forum:
  


Current Time: Mon Jul 28 21:44:47 CDT 2014

Total time taken to generate the page: 0.96247 seconds