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

ASC and DESC are keywords. CASE statements are going to evaluate to a value, not a keyword. One option would be dynamic SQL (build a string and then execute), another option would be to have an IF clause at a higher level. If you were only sorting by numbers, you could multiply by negative 1, but strings are tougher. I guess you could do something crazy like having a second table that stores strings and sort order as a number, and multiplying that number by -1, but I have a hard time believing that would be better in any conceivable way than the first two options.

Answer 2

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

Answer 3

The alternative to a cut'n'pasted CASE() statement is to shape the query in-flight with dynamic SQL.

Here is the simplest demo possible.

create or replace function get_emps
    ( p_dno in emp.deptno%type
      , p_sort_col in varchar2 := 'EMPNO'
      , p_asc_desc in varchar2 := 'ASC')
    return sys_refcursor
is
    stmt varchar2(32767);
    return_value sys_refcursor;
begin

    stmt := 'select * from emp where deptno = :1';

    if p_sort_col is not null 
    then 
        stmt := stmt ||' order by '||p_sort_col||' '||p_asc_desc;
    end if;

    open return_value for stmt using p_dno;

    return return_value;

end get_emps;
/

And this is it in action ...

SQL> var rc refcursor
SQL> exec :rc := get_emps(20, 'ENAME')

PL/SQL procedure successfully completed.

SQL> print rc

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 CLARKE     CLERK           7902 17-DEC-80        800                    20
      7902 GASPAROTTO ANALYST         7566 03-DEC-81       3000                    20
      7876 KULASH     CLERK           7788 23-MAY-87       1100                    20
      7788 RIGBY      ANALYST         7566 19-APR-87       3000                    20
      7566 ROBERTSON  MANAGER         7839 02-APR-81       2975                    20

SQL> exec :rc := get_emps(20, 'SAL', 'DESC')

PL/SQL procedure successfully completed.

SQL> print rc

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 RIGBY      ANALYST         7566 19-APR-87       3000                    20
      7902 GASPAROTTO ANALYST         7566 03-DEC-81       3000                    20
      7566 ROBERTSON  MANAGER         7839 02-APR-81       2975                    20
      7876 KULASH     CLERK           7788 23-MAY-87       1100                    20
      7369 CLARKE     CLERK           7902 17-DEC-80        800                    20

SQL>