how to get this order by working in oracle pl/sql

Question

ORDER BY CASE
   WHEN v_SORT_TYPE = 'ASC' THEN
        CASE 
           WHEN v_SORT_ORDER = 'lname' THEN CAST(lname AS VARCHAR2(45)) || ',' || ROWNUM
           WHEN v_SORT_ORDER = 'code' THEN CAST(code AS VARCHAR2(52)) || ',' || ROWNUM
        END ASC
   WHEN v_SORT_TYPE = 'DSC' THEN
        CASE 
           WHEN v_SORT_ORDER = 'lname' THEN CAST(lname AS VARCHAR2(45)) || ',' || ROWNUM
           WHEN v_SORT_ORDER = 'code' THEN CAST(code AS VARCHAR2(52)) || ',' || ROWNUM
        END DESC                   
END                   

I am trying to write conditions for when v_SORT_TYPE is passed in as ASC or DSC. I am getting compilation error in this.

I assume this would be a common thing to do in my pl/sql SP's.

where am I going wrong?

Answer 1

You need to rethink the order by into multiple "columns".

ORDER BY
  CASE WHEN v_sort_type = 'ASC' AND v_sort_order = 'lname' THEN lname END ASC,
  CASE WHEN v_sort_type = 'DESC' AND v_sort_order = 'lname' THEN lname END DESC,
  CASE WHEN v_sort_type = 'ASC' AND v_sort_order = 'code' THEN cname END ASC,
  CASE WHEN v_sort_type = 'DESC' AND v_sort_order = 'code' THEN cname END DESC

Only one of those would be in effect at any one time, so you'd effectively get (for example)

 ORDER BY null ASC,null DESC,code ASC, null DESC

or

 ORDER BY null ASC,lname DESC,null ASC, null DESC