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.

8 comments:

Johannes de Jong said...
This comment has been removed by the author.
Johannes de Jong said...
This comment has been removed by the author.
Johannes de Jong said...

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.

Anonymous said...

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

Denes Kubicek said...

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

Denes Kubicek said...

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

Johannes de Jong said...

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.

Denes Kubicek said...

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