-- PORTFOLOIO ¤ WHITE PAPERS ¤ HOME PAGE
drop table t;
Table dropped
-- Using the sign function to make decode determine
-- greater than, less than, greater than or equal to, less than or equal to.
--
-- I found this on the asktom.oracle.com website.
-- I have used it so many times since I found it that I just want to keep a record of it here.
create table t (x number);
Table created
insert into t (x) values(0);
1 row inserted
insert into t (x) values (1);
1 row inserted
insert into t (x) values (2);
1 row inserted
insert into t (x) values (3);
1 row inserted
insert into t (x) values (4);
1 row inserted
insert into t (x) values (5);
1 row inserted
commit;
Commit complete
select * from t
X
--
0
1
2
3
4
5
6 rows selected
/
select x,
decode( sign(x-1),
-1, 'Less than 1',
'Greater than or equal to 1' ) c1,
decode( sign(x-2),
-1, 'Less than 2',
decode( sign(x-4),
1, 'Greater than 4',
'Between 2 and 4' ) ) c2,
decode( sign(x-4),
1, 'Greater than 4',
'Less than 4' ) c3
from t order by x asc
X C1 C2 C3
-- -------------------------- --------------- --------------
0 Less than 1 Less than 2 Less than 4
1 Greater than or equal to 1 Less than 2 Less than 4
2 Greater than or equal to 1 Between 2 and 4 Less than 4
3 Greater than or equal to 1 Between 2 and 4 Less than 4
4 Greater than or equal to 1 Between 2 and 4 Less than 4
5 Greater than or equal to 1 Greater than 4 Greater than 4
6 rows selected
/
select x,
decode( sign(x-1),
-1, 'Less than 1',
null ) c1,
decode( sign(x-2),
-1, null,
decode( sign(x-4),
1, null,
'Between 2 and 4' ) ) c2,
decode( sign(x-4),
1, 'Greater than 4',
null ) c3
from t order by x asc
X C1 C2 C3
-- ----------- --------------- --------------
0 Less than 1
1
2 Between 2 and 4
3 Between 2 and 4
4 Between 2 and 4
5 Greater than 4
6 rows selected
/