On Feb 10, 10:44 am, Andrej Hopko <[email protected]> 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 [email protected]
> > To unsubscribe from this group, send email to
> > [email protected]
> > 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
