-- 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
 
 
/