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,'EMP开发者_Go百科LOYEE'
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??
ORDER BY DECODE(
GRADE_MASTER.GRADE_DESCRIPTION,
'SENIOR DIRECTOR', 1,
'DIRECTOR', 2,
'MANAGER', 3,
'EMPLOYEE', 4,
5)
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.
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.
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.
精彩评论