Search code examples
oracleplsqlprocedure

Oracle SQL order by case condition

I have problem how to write sql statement with ORDER BY and CASE over parameter

Input parameter:

p_desc = 0
p_sort_by = 'name'

ORDER BY 
  CASE NVL(p_desc, 0) WHEN 1 THEN NVL(p_sort_by, 1) END DESC,
  CASE NVL(p_desc, 0) WHEN 0 THEN NVL(p_sort_by, 1) END ASC
Solution

  • White-list all the combinations using CASE expressions in the ORDER BY clause. When the condition is not matched then the CASE expression will return NULL and all rows will have the same ordering for that un-matched CASE expression so only the matched CASE expression will have any effect on the ordering.

    ORDER BY 
  CASE WHEN p_sort_by = 'COLUMN1' AND NVL(p_desc, 0) = 0 THEN column1 END ASC,
  CASE WHEN p_sort_by = 'COLUMN1' AND p_desc = 1         THEN column1 END DESC,
  CASE WHEN p_sort_by = 'COLUMN2' AND NVL(p_desc, 0) = 0 THEN column2 END ASC,
  CASE WHEN p_sort_by = 'COLUMN2' AND p_desc = 1         THEN column2 END DESC,
  CASE WHEN p_sort_by = 'COLUMN3' AND NVL(p_desc, 0) = 0 THEN column3 END ASC,
  CASE WHEN p_sort_by = 'COLUMN3' AND p_desc = 1         THEN column3 END DESC,
  CASE WHEN p_sort_by = 'COLUMN4' AND NVL(p_desc, 0) = 0 THEN column4 END ASC,
  CASE WHEN p_sort_by = 'COLUMN4' AND p_desc = 1         THEN column4 END DESC