Using DECODE in ORDER BY and GROUP BY Clauses

Using DECODE in ORDER BY and GROUP BY Clauses :  You may need to specify many varying ORDER BY clauses to get the result you want. Rather than coding many identical queries, each with a different ORDER BY clause, you can specify a DECODE function such as the following:

SELECT . . .
 FROM   emp
 WHERE  emp_name LIKE 'SMITH%'
 ORDER
       BY DECODE(:BLK.SEQN_FLD 'E', emp_no, 'D', dept_no);

This approach can be extended further to include the GROUP BY clause:

   SELECT . . .
   FROM   emp
   WHERE  emp_name  LIKE  'SMITH%'
   GROUP
       BY DECODE(:INPUT,'E',emp_no,'D',dept_no);

Note:  DECODE verbs within ORDER BY and GROUP BY statements cannot use indexes. Instead, an internal sort is required. Because this is a slow process, use DECODE within ORDER BY only for online statements in which the number of rows returned by the WHERE clause is small. For reports, you need not worry about limits.