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
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