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:
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.
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
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
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
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.
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
Post a Comment