I have a query that returns all employees plus their supervisors (based on reviews from the prior 5 years) then orders by
- UOID and
- Contract type (Primary, Secondary, Other) and then
- Review date
So if the employee has multiple positions we get the primary position first.
If they have had multiple reviews the latest one floats to the top.
Now we can use the first row returned for each EE to determine
- their current primary org. and
- their most recent supervisor (if there’s a review on file)
To do the custom order by I used the decode function on the employee’s contract type. *Thanks to http://psoug.org/reference/decode_case.html (More Complex DECODE), and http://psoug.org/reference/orderby.html (Ordering With A Decode)
Here’s the whole order by line:
ORDER BY HRISMGR.EMPLOYEE.ID, DECODE(HRISMGR.JOB_GENERAL.CONTRACT_TYPE, 'P', 'A', 'S', 'B', 'O', 'C', 'Z'), HRISMGR.REVIEW.REVT_DATE DESC;
but let’s isolate the fun part:
DECODE(HRISMGR.JOB_GENERAL.CONTRACT_TYPE, 'P', 'A', 'S', 'B', 'O', 'C', 'Z')
Here I used decode like a case statement:
Switching on the contract_type;
if ‘P’ (Primary) then return ‘A’ for sorting
If ‘S’ (Secondary) then return ‘B’ for sorting
If ‘O’ (Other) then return ‘C’ for sorting
Default case: return ‘Z’ for sorting
It works great.