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 Tue, 10 September 2013 02:35
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
Re: Next multiple of 10 value for the given integer [message #595315 is a reply to message #595314] Tue, 10 September 2013 02:39
 pablolee Messages: 2844Registered: 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
 Lalit Kumar B Messages: 3123Registered: 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
 Maaher Messages: 7062Registered: 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
 Michel Cadot Messages: 65090Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Maaher Messages: 7062Registered: 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
 Michel Cadot Messages: 65090Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Bill B Messages: 1655Registered: 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
 Michel Cadot Messages: 65090Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 John Watson Messages: 7008Registered: 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
 ebrian Messages: 2794Registered: 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
 John Watson Messages: 7008Registered: 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
 ebrian Messages: 2794Registered: 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
 Littlefoot Messages: 21131Registered: June 2005 Location: Croatia, Europe Senior MemberAccount 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
 John Watson Messages: 7008Registered: 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
 ebrian Messages: 2794Registered: 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