Order by in subquery (for jQuery jTable) doesn't work?

Question

Some background:

My framework jQuery jTable, allows me to do pagination and sort columns, in my select query I need to retrieve n rows (from nth, to nth) and previously order the data by the selected column.

I have a table with n columns where would not exist some rows (this is an example):

To achieve the first requirement I wrote the follow procedure:

create or replace 
PROCEDURE PR_SHOWVALUESOLD 
(
  PRMROWMIN IN NUMBER  
, PRMROWMAX IN NUMBER  
, CURSORRESULT OUT SYS_REFCURSOR
) AS 
BEGIN
  open CURSORRESULT for
    select * from 
      (select v.*, rownum r,
                            (
                              select count(*) TOTALITEMS from TABLE1 v
                            ) TOTALITEMS
              from TABLE1 v
      ) d
     where d.r >= PRMROWMIN and d.r <= PRMROWMAX;

END PR_SHOWVALUESOLD;

This work successfully, I execute the procedure with the follows parameters (PRMROWMIN = 6, PRMROWMAX = 9), the result of the procedure are in Output Varibles window.

Now comes the next step, I need to order the data before take from n to x row.

I rewrite the procedure to do this, but doesn't work:

CREATE OR REPLACE PROCEDURE PR_SHOWVALUES 
(
  PRMROWMIN IN NUMBER  
, PRMROWMAX IN NUMBER  
, PRMORDERCOL IN VARCHAR2  
, PRMORDERDIR IN VARCHAR2  
, CURSORRESULT OUT SYS_REFCURSOR
) AS 
BEGIN
  open CURSORRESULT for
    select * from 
      (select v.*, rownum r,
                            (
                              select count(*) TOTALITEMS from TABLE1 v
                            ) TOTALITEMS
              from TABLE1 v
              order by 'LOWER(' || PRMORDERCOL || ')' || ' ' || PRMORDERDIR
      ) d
     where d.r >= PRMROWMIN and d.r <= PRMROWMAX;

END PR_SHOWVALUES;

I executed the modified procedure with the follows parameters:

  PRMROWMIN := 6;
  PRMROWMAX := 9;
  PRMORDERCOL := 'COLUMNA';
  PRMORDERDIR := 'DESC';

I expected the highlighted rows Query Result 2 window (but this new procedure retrieve the same data as old but disordered Output Variables Window):

How to achieve my requirements?

Thanks in advance.

Answer 1

This is your order by:

          order by 'LOWER(' || PRMORDERCOL || ')' || ' ' || PRMORDERDIR

It is not applying the function lower(). Instead, it is concatenating the strings. You may mean:

          order by LOWER(PRMORDERCOL) ' ' || PRMORDERDIR

Answer 2

I found a solution to my requirement.

• I need to use DECODE to match every column to sort.
• I can order in subquery, in this case I do two order by in two subqueries.

The documentation of PL/SQL DECODE function are in: PL/SQL DECODE FUNCTION

The final Procedure are:

CREATE OR REPLACE PROCEDURE PR_SHOWVALUES 
(
  PRMROWMIN IN NUMBER  
, PRMROWMAX IN NUMBER  
, PRMORDERCOL IN VARCHAR2  
, PRMORDERDIR IN VARCHAR2  
, CURSORRESULT OUT SYS_REFCURSOR
) AS 
BEGIN

  open CURSORRESULT for
    select * from (
      select rownum r, v.* from 
          (
            select * from
            (
              select * from table1 tbl
              order by decode
              (
                UPPER(PRMORDERCOL),
                'COLUMNA', LOWER(tbl.COLUMNA),
                'COLUMNB', LOWER(tbl.COLUMNB),
                LOWER(tbl.TABLE1_ID)
              )
            )
            ORDER BY
              CASE
                WHEN UPPER(PRMORDERDIR) = 'DESC' THEN
                  ROWNUM * -1
                ELSE
                  ROWNUM
              END    
          ) v
    )
    where r >= PRMROWMIN and r <= PRMROWMAX;

END PR_SHOWVALUES;

Acknowledgment to Jack David Baucum where I found the solution.