Thanks guys. It resolved my problem :)
On Fri, Feb 10, 2012 at 12:12 PM, ddf <orat...@msn.com> wrote: > > > On Feb 10, 10:44 am, Andrej Hopko <ado.ho...@gmail.com> wrote: > > Hi, > > just simple suggestion - what about dividing number by 25 (which is step > > for your ROUND), rounding the decimal to nearest integer (standard ROUND > > function should by sufficient) and then multiplying integer back with 25 > > - the solution on level of two years programming experience at high > > school :D > > > > hope I was of some help > > > > btw. although oracle has ROUND with second paramter - as a grade of > > ROUND, it works only for integer grades of 10 to round: > > > > SELECT ROUND(113.34,-1.5) c1 > > , ROUND(113.34,-LOG(10, 25)) c2 > > , ROUND(113.34,-1.5) c2 > > , ROUND(113.34,-2) c2 > > FROM DUAL > > ; > > > > C1 C2 C2 C2 > > ---------------------- ---------------------- ---------------------- > > ---------------------- > > 110 110 110 100 > > > > I found this interesting news :) > > > > best regards > > hoppo > > > > On 10. 2. 2012 18:13, swaroop gowda wrote: > > > > > > > > > Hi All, > > > I have total amount like 110.01, 113.34, 126.01, 145.54, 155.01 etc > > > but I need to round this 100 or 125 or 150 etc. > > > > > Please find my requirement below, > > > 1) I need to round to 100 if the total is between 100 to 112. > > > 2) I need to round to 125 if the total is between 113 to 125. > > > 3) I need to round to 125 if the total is between 116 to 137. > > > 4) I need to round to 150 if the total is between 138 to 150. > > > 5) I need to round to 150 if the total is between 151 to 167. > > > 5) I need to round to 200 if the total is between 168 to 200. > > > > > You help is greatly appreciated. > > > -- > > > Thanks & Regards > > > Swaroop Thailuru Swamy > > > Ph: 713-392-1571 > > > > > -- > > > You received this message because you are subscribed to the Google > > > Groups "Oracle PL/SQL" group. > > > To post to this group, send email to Oracle-PLSQL@googlegroups.com > > > To unsubscribe from this group, send email to > > > oracle-plsql-unsubscr...@googlegroups.com > > > For more options, visit this group at > > >http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text - > > > > - Show quoted text - > > I will agree this problem can be solved outside of PL/SQL and is > probably better suited to one of the more generic Oracle newsgroups. > However solutions can be offered using both: > > SQL> create table round_amt( > 2 amt number(9,2) > 3 ); > > Table created. > > SQL> > SQL> insert all > 2 into round_amt > 3 values(110.25) > 4 into round_amt > 5 values(120.25) > 6 into round_amt > 7 values(113.25) > 8 into round_amt > 9 values(117.25) > 10 into round_amt > 11 values(116.25) > 12 into round_amt > 13 values(190.25) > 14 into round_amt > 15 values(180.25) > 16 into round_amt > 17 values(160.25) > 18 into round_amt > 19 values(150.25) > 20 into round_amt > 21 values(200.25) > 22 select * From dual; > > 10 rows created. > > SQL> > SQL> commit; > > Commit complete. > > SQL> > SQL> -- > SQL> -- Straight SQL solution > SQL> -- > SQL> > SQL> select amt, > 2 case when trunc(amt) between 110 and 112 then 110 > 3 when trunc(amt) between 113 and 137 then 125 > 4 when trunc(amt) between 138 and 167 then 150 > 5 when trunc(amt) between 168 and 200 then 200 end > rounded_amt > 6 from round_amt; > > AMT ROUNDED_AMT > ---------- ----------- > 110.25 110 > 120.25 125 > 113.25 125 > 117.25 125 > 116.25 125 > 190.25 200 > 180.25 200 > 160.25 150 > 150.25 150 > 200.25 200 > > 10 rows selected. > > SQL> > SQL> -- > SQL> -- PL/SQL solution > SQL> -- > SQL> > SQL> declare > 2 cursor get_amt is > 3 select amt, trunc(amt) t_amt > 4 from round_amt; > 5 begin > 6 for i in get_amt loop > 7 if i.t_amt between 100 and 112 then > 8 dbms_output.put_line(i.amt||' rounded is > 100'); > 9 elsif i.t_amt between 113 and 137 then > 10 dbms_output.put_line(i.amt||' rounded is > 125'); > 11 elsif i.t_amt between 138 and 167 then > 12 dbms_output.put_line(i.amt||' rounded is > 150'); > 13 elsif i.t_amt between 168 and 200 then > 14 dbms_output.put_line(i.amt||' rounded is > 200'); > 15 else > 16 dbms_output.put_line(i.amt||' is out of > range'); > 17 end if; > 18 > 19 end loop; > 20 > 21 end; > 22 / > 110.25 rounded is 100 > 120.25 rounded is 125 > 113.25 rounded is 125 > 117.25 rounded is 125 > 116.25 rounded is 125 > 190.25 rounded is 200 > 180.25 rounded is 200 > 160.25 rounded is 150 > 150.25 rounded is 150 > 200.25 rounded is 200 > > PL/SQL procedure successfully completed. > > SQL> > > David Fitzjarrell > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- Thanks & Regards Swaroop Thailuru Swamy Ph: 713-392-1571 -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
