Monday, 30 June 2008

Sorting with ROWNUM

Creating a query and including the ROWNUM will work only for those reports, with disabled column sorting. Once you enable the column sorting, the ROWNUM will not show the right order. This is because ApEx does the sorting after determining the ROWNUM.

A simple trick can help you to overcome this issue. Just escape the ApEx internal #ROWNUM# like this:


SELECT '#ROWNUM#' SEQUENCE, empno, ename, sal
  FROM emp;


and it will give you the right ROWNUM regardless of your sorting.



You can see that working in this example in my Demo Application.

on

8 comments:

  1. Johannes de Jong1 July 2008 at 06:43

    This comment has been removed by the author.

    ReplyDelete
  2. Johannes de Jong1 July 2008 at 06:45

    This comment has been removed by the author.

    ReplyDelete
  3. Johannes de Jong1 July 2008 at 06:46

    TX Denes.

    One quick question though, My query results in more than 15 rows, 15 being the # rows per page, when I go to the next page the count starts at 1 again.

    Is there anyway that it continues counting, ie the 2nd page starting at 16 instrad of at 1.

    ReplyDelete
  4. Anonymous1 July 2008 at 15:24

    Denes,

    Another great post, but is there any reason you are still linking to htmldb.oracle.com on your blog and in the forum? The name HTML DB was changed to APEX a long time ago as I'm sure you know. At some point it's pretty likely the htmldb.oracle.com domain name will no longer work and it also could be confusing to new users.

    Thanks,
    Tyler

    ReplyDelete
  5. Denes Kubicek1 July 2008 at 18:37

    Tyler,

    Thanks for pointing that out. I haven't noticed that since I have the links in my bookmark and they work ;) I will change that.

    Denes

    ReplyDelete
  6. Denes Kubicek2 July 2008 at 16:21

    Johannes,

    If you want to count the pagination you could get the min_row value and add it to the #ROWNUM#.

    You can do that using:

    apex_application.g_flow_current_min_row

    Denes

    ReplyDelete
  7. Johannes de Jong2 July 2008 at 18:54

    TX Denes,

    I must say I'm totally unfamiliar with apex_application.g_flow_current_min_row , simply typing: '#ROWNUM#'+apex_application.g_flow_current_min_row
    gives a parsing error.


    I'll have to do a bit more "research" I think :-)

    Tx for the tip though & helping me expand my knowledge of Apex.

    ReplyDelete
  8. Denes Kubicek3 July 2008 at 11:41

    Johannes,

    Create a hidden item and create a computation of type PL/SQL Expression. There, you put

    apex_application.g_flow_current_min_row

    Use this in your SQL. However, you got to think about converting the values for the #ROWNUM# into Number first (I think) and then add this to the value of the hidden item.

    Try that out.

    Denes

    ReplyDelete