1

I have a Oracle procedure inside a package like this

PROCEDURE getEmployee
(
  pinLanguage               IN    VARCHAR2,
  pinPage                   IN    NUMBER,
  pinPageSize               IN    NUMBER,
  pinSortColumn             IN    VARCHAR2,
  pinSortOrder              IN    VARCHAR2,
  poutEmployeeCursor        OUT   SYS_REFCURSOR
)
AS
BEGIN
    OPEN poutEmployeeCursor FOR
    SELECT * FROM (
      SELECT EMPLOYEE_ID, USERNAME, FULL_NAME, DATE_OF_BIRTH, EMP.GENDER_ID, GEN_TR.GENDER, EMP.WORK_TYPE_ID, WT_TR.WORK_TYPE, SALARY, EMAIL, PROFILE_IMAGE,
      ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID ASC) RN

      FROM EMPLOYEES EMP
      INNER JOIN GENDERS GEN ON EMP.GENDER_ID = GEN.GENDER_ID
      LEFT JOIN GENDERS_MLD GEN_TR ON GEN.GENDER_ID = GEN_TR.GENDER_ID AND GEN_TR.LANGUAGE = pinLanguage
      INNER JOIN WORK_TYPES WT ON EMP.WORK_TYPE_ID = WT.WORK_TYPE_ID
      LEFT JOIN WORK_TYPES_MLD WT_TR ON WT.WORK_TYPE_ID = WT_TR.WORK_TYPE_ID AND WT_TR.LANGUAGE = pinLanguage
    )
    WHERE RN BETWEEN (((pinPage - 1) * pinPageSize) + 1) AND (pinPage * pinPageSize);
END;

I need to make the sort order of the above query dynamic

If I pass the text FullName to pinSortColumn parameter, it need to sort FULL_NAME column

If I pass the text DateOfBirth to pinSortColumn parameter, it need to sort DATE_OF_BIRTH column

If I pass the text Gender to pinSortColumn parameter, it need to sort GEN_TR.GENDER column

I can pass the text asc or desc to pinSortOrder parameter and the query need to be sorted accordingly. Can you please help me to achieve this?

3 Answers3

1

You can use separate order by for asc and desc as following:

ORDER BY 
CASE pinSortOrder WHEN 'asc' THEN
    CASE pinSortColumn 
      WHEN 'FullName' THEN FULL_NAME 
      WHEN 'DateOfBirth' THEN to_char(DATE_OF_BIRTH,'yyyymmddhh24miss')
      WHEN 'Gender' THEN GEN_TR.GENDER   
    END
END,
CASE pinSortOrder WHEN 'desc' THEN
    CASE pinSortColumn 
      WHEN 'FullName' THEN FULL_NAME 
      WHEN 'DateOfBirth' THEN to_char(DATE_OF_BIRTH,'yyyymmddhh24miss')
      WHEN 'Gender' THEN GEN_TR.GENDER   
    END
END DESC

Let's say you have passed pinSortColumn as FullName and pinSortOrder as asc then order by clause will be ORDER BY FULL_NAME, NULL DESC (please note that default order will be asc so I have not write it in the code. Query will be ordered by FULL_NAME in ascending manner)

Now, If you have passed pinSortColumn as FullName and pinSortOrder as desc then order by clause will be ORDER BY NULL, FULL_NAME DESC.

Null will not impact ordering.

I hope it is clear now.

Cheers!!

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • This is a good point but it always order in ASC. I cannot order in DESC. – Autumn Valentine Aug 19 '19 at 02:25
  • `DESC` is there in the second case where we are comparing `pinSortOrder` with `desc`. You can try it. It was working smoothly in my local example – Popeye Aug 19 '19 at 02:45
  • Sorry, I mean it sort `DESC` by default. But I cannot sort in `ASC`. Acording to your example, `DESC` is out of `CASE` statement. So I am also confused how it can sort in `ASC`? – Autumn Valentine Aug 19 '19 at 03:42
  • I have updated my answer with description. Please check now – Popeye Aug 19 '19 at 04:40
  • Thank you got your point and working. Now I have a new error when sorting **DATE_OF_BIRTH** `Error(316,51): PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got DATE` – Autumn Valentine Aug 19 '19 at 05:28
  • Because `DATE_OF_BIRTH` is date and the other two fields are `CHAR`. You can try `to_char(DATE_OF_BIRTH,'yyyymmddhh24miss')` in `order by`. I have updated my answer accordingly. Please check now – Popeye Aug 19 '19 at 05:46
  • I assume you need `CASE pinSortOrder WHEN 'desc' THEN ... ELSE NULL END`, otherwise you get an **ORA-06592: CASE not found while executing CASE statement** - but I am not sure about that. – Wernfried Domscheit Aug 19 '19 at 05:58
  • No, It will return NULL if the condition is not satisfied – Popeye Aug 19 '19 at 06:17
0

Try this one:

WHERE ...
ORDER BY 
   CASE pinSortColumn 
      WHEN 'FullName' THEN FULL_NAME 
      WHEN 'DateOfBirth' THEN DATE_OF_BIRTH
      WHEN 'Gender' THEN GEN_TR.GENDER   
   END;

However, I don't think you can use ASC, DESC is this way. You to create a dynamic query. For an OPEN ... FOR ... statement it is trivial:

sqlstr := 'SELECT * FROM (
      SELECT EMPLOYEE_ID, USERNAME, FULL_NAME, DATE_OF_BIRTH, EMP.GENDER_ID, GEN_TR.GENDER, EMP.WORK_TYPE_ID, WT_TR.WORK_TYPE, SALARY, EMAIL, PROFILE_IMAGE,
      ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID ASC) RN

      FROM EMPLOYEES EMP
      INNER JOIN GENDERS GEN ON EMP.GENDER_ID = GEN.GENDER_ID
      LEFT JOIN GENDERS_MLD GEN_TR ON GEN.GENDER_ID = GEN_TR.GENDER_ID AND GEN_TR.LANGUAGE = :pinLanguage
      INNER JOIN WORK_TYPES WT ON EMP.WORK_TYPE_ID = WT.WORK_TYPE_ID
      LEFT JOIN WORK_TYPES_MLD WT_TR ON WT.WORK_TYPE_ID = WT_TR.WORK_TYPE_ID AND WT_TR.LANGUAGE = :pinLanguage
    )
    WHERE RN BETWEEN (((:pinPage - 1) * :pinPageSize) + 1) AND (:pinPage * :pinPageSize) 
    ORDER BY '

CASE pinSortColumn 
   WHEN 'FullName' THEN sqlstr := sqlstr || 'FULL_NAME ';
   WHEN 'DateOfBirth' THEN sqlstr := sqlstr || 'DATE_OF_BIRTH ';
   WHEN 'Gender' THEN sqlstr := sqlstr || 'GEN_TR.GENDER ';
END CASE;   
sqlstr := sqlstr || pinSortOrder;

OPEN poutEmployeeCursor FOR sqlstr USING pinLanguage, pinLanguage, pinPage, pinPageSize, pinPage, pinPageSize;

Not in case you run Oracle 12c or newer you can use the Row Limiting Clause instead of dealing with row number.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
0

You can avoid all these duplicated cases. Multiply row number by -1 when descending order is required:

order by 
    case pinSortOrder when 'desc' then -1 else 1 end * 
    row_number() over (
      order by case pinSortColumn when 'FullName'    then full_name     end, 
               case pinSortColumn when 'Gender'      then gender        end, 
               case pinSortColumn when 'DateOfBirth' then date_of_birth end)  

dbfiddle demo

Also this way you do not have to convert data the same type, no need to use to_char.

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24