There are two common approaches to changing the order of records in Oracle development: dynamic SQL and decode functions.
I recommend using functions--they have both flexibility and easy-to-understand code. Here is an example:
CREATE OR REPLACE FUNCTION EMP_ORDER(p_type IN number, p_salary IN number, p_comm IN number) RETURN NUMBER IS BEGIN if p_type = 1 then -- by salary RETURN p_salary; end if; if p_type = 2 then -- by comm/salary RETURN nvl(p_comm,0)/p_salary; end if; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(sqlerrm); RETURN 0; END EMP_ORDER; / prompt order employees dynamically prompt order by salary SELECT ename, comm, sal FROM emp ORDER by EMP_order(1, sal, comm) desc; prompt order by comm/salary SELECT ename, comm, sal FROM emp ORDER by EMP_order(2, sal, comm) desc;
In addition to using functions in ORDER BY clauses, it is sometimes beneficial to use them in GROUP BY clauses. For example:
CREATE OR REPLACE FUNCTION EMP_TIER(p_salary IN number) RETURN varchar2 IS BEGIN if nvl(p_salary,0) < 1000 then -- junior RETURN 'junior'; elsif nvl(p_salary,0) < 2000 then -- intermediate RETURN 'intermediate'; else -- senior RETURN 'senior'; end if; EXCEPTION WHEN OTHERS THEN RETURN 'error'; END EMP_TIER; / prompt cluster employees by salary column tier format a15 select EMP_TIER(sal) tier, count(1) Count_Employees from emp group by EMP_TIER(sal);
For More Information
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The best Oracle web links: tips, tutorials, and more.
- Have another Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Do you have any technical questions about Oracle administration or development? Post them--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature! Our Oracle gurus are waiting to answer your toughest Oracle questions.