Use least() to limit the value
SQL> create table salary
2 ( grade NUMBER(2) constraint S_PK primary key
3 , lowerlimit NUMBER(6,2)
4 , upperlimit NUMBER(6,2)
5 , bonus NUMBER(6,2)
6 , constraint S_LO_UP_CHK check(lowerlimit <= upperlimit)
7 ) ;
Table created.
SQL>
SQL> insert into salary values (1, 700,1200, 0);
1 row created.
SQL> insert into salary values (2, 1201,1400, 50);
1 row created.
SQL> insert into salary values (3, 1401,2000, 100);
1 row created.
SQL> insert into salary values (4, 2001,3000, 200);
1 row created.
SQL> insert into salary values (5, 3001,9999, 500);
1 row created.
SQL>
SQL> select grade + 5
2 , lowerlimit + 2300
3 , least(9999,upperlimit + 2300)
4 , 500
5 from salary;
GRADE+5 LOWERLIMIT+2300 LEAST(9999,UPPERLIMIT+2300) 500
---------- --------------- --------------------------- ----------
6 3000 3500 500
7 3501 3700 500
8 3701 4300 500
9 4301 5300 500
10 5301 9999 500
SQL>
SQL>
SQL> drop table salary;
Table dropped.
Related examples in the same category