Custom order in Oracle PL/SQL

Question

I have an oracle query in which and i want the result to be in custom order 'SENIOR DIRECTOR', 'DIRECTOR', 'MANAGER', 'EMPLOYEE' which is from the field GRADE_DESCRIPTON. I am using the below query.

However I am not getting the desired result The order of the result im getting is 'SENIOR DIRECTOR','MANAGER', DIRECTOR,'EMPLOYEE'

SELECT DISTINCT GRADE_DESCRIPTION
      , HIRING_FORECATS.*  
FROM GRADE_MASTER left join     HIRING_FORECATS 
   ON (HIRING_FORECATS.GRADE = GRADE_MASTER.GRADE_DESCRIPTION 
        and HIRING_FORECATS.LOCATION = 'HO' )   
 order by decode    
 (GRADE_MASTER.GRADE_DESCRIPTION, 'SENIOR DIRECTOR', 'DIRECTOR', 'MANAGER', 'EMPLOYEE')

Any Suggestions??

Answer 1

ORDER BY DECODE(
         GRADE_MASTER.GRADE_DESCRIPTION,
         'SENIOR DIRECTOR', 1,
         'DIRECTOR', 2,
         'MANAGER', 3,
         'EMPLOYEE', 4,
         5)

Answer 2

You could add a new column to GRADE_MASTER to represent the ordering or levels. This would allow you to change the order by clause to:

ORDER BY GRADE_MASTER.GRADE_ORDER

And has the advantage of only requiring changes to the GRADE_MASTER table when a new grade is introduced.

Answer 3

The point about decode() is that for each pair of values it substitutes teh second value for the first. So your posted query sorts 'SENIOR DIRECTOR' as 'DIRECTOR', 'MANAGER' as 'EMPLOYEE' and everybody else randomly after that.

So what you need to do is assign a sort order to each value. The most future-proof way of handling this would be to assign a SORT_ORDER column to the GRADE_DESCRIPTION table, but that is not always practical. So if you only have one or two reports which need to be sorted in this way (seems likely) then you can just continue with the hard-coded descriptions:

SELECT DISTINCT GRADE_DESCRIPTION
      , HIRING_FORECATS.*  
FROM GRADE_MASTER left join     HIRING_FORECATS 
   ON (HIRING_FORECATS.GRADE = GRADE_MASTER.GRADE_DESCRIPTION 
        and HIRING_FORECATS.LOCATION = 'HO' )   
 order by decode    
 (GRADE_MASTER.GRADE_DESCRIPTION
    , 'SENIOR DIRECTOR', 10
      , 'DIRECTOR', 20
      , 'MANAGER', 30
      , 'EMPLOYEE', 40
      , 100)

It is a good idea to include a default value, just in case. I also like to leave large gaps in the sort numbers, to make it easier to slot in a new value.

Answer 4

You should use a SQL CASE instead of a PL/SQL DECODE:

order by CASE WHEN GRADE_MASTER.GRADE_DESCRIPTION = 'SENIOR DIRECTOR' THEN 1 WHEN GRADE_MASTER.GRADE_DESCRIPTION = 'DIRECTOR' THEN 2 WHEN GRADE_MASTER.GRADE_DESCRIPTION = 'MANAGER' THEN 3 WHEN GRADE_MASTER.GRADE_DESCRIPTION = 'EMPLOYEE' THEN 4 ELSE 5 END

This will keep the parser from switching between SQL and PL/SQL modes.