Home » SQL & PL/SQL » SQL & PL/SQL » Row counter on multi-table view or query
Row counter on multi-table view or query [message #39940] Mon, 26 August 2002 15:34 Go to next message
Eric
Messages: 78
Registered: April 1999
Member
How can I display a row counter for a view or query with a multi-table join.
The view will be sorted and resorted on different fields by the application, so the counter must reset itself everytime the view is called.
There are no keys to use that will suffice.

The ROWNUM will not work, unfortunately, due to the varying sorting requirements.

I am thinking about a sequence or procedure dedicated to this requirement.

The counter will be used to slice out pieces of the result set for page up and page down functionality on the display.

CREATE OR REPLACE VIEW "PLFEP2"."F2_PL_DEPT_ROOM_TEST_V" ("RMCDE_CODE_CD","RMCDE_NAME_NM","DPTRMM_SUBCODE_IK","PRJDPT_IK","DPTRM_IK","DPTRMM_IK","DPTRM_LOCALNAME_TX","DPTRM_RMQTY_QT","DPTRM_COMMENT_TX")
AS
SELECT
F2_RR_ROOM_CODE_T.RMCDE_CODE_CD,
F2_RR_ROOM_CODE_T.RMCDE_NAME_NM,
F2_RR_DEPT_ROOMMSTR_T.DPTRMM_SUBCODE_IK,
F2_PL_DEPT_ROOM_T.PRJDPT_IK,
F2_PL_DEPT_ROOM_T.DPTRM_IK,
F2_PL_DEPT_ROOM_T.DPTRMM_IK,
F2_PL_DEPT_ROOM_T.DPTRM_LOCALNAME_TX,
F2_PL_DEPT_ROOM_T.DPTRM_RMQTY_QT,
F2_PL_DEPT_ROOM_T.DPTRM_COMMENT_TX

FROM
RRFEP2.F2_RR_ROOM_CODE_T,
RRFEP2.F2_RR_DEPT_ROOMMSTR_T,
PLFEP2.F2_PL_DEPT_ROOM_T,
PLFEP2.F2_PL_PRJCT_DEPT_T

WHERE
F2_RR_ROOM_CODE_T.RMCDE_IK = F2_RR_DEPT_ROOMMSTR_T.RMCDE_IK AND
F2_RR_DEPT_ROOMMSTR_T.DPTRMM_IK = F2_PL_DEPT_ROOM_T.DPTRMM_IK AND
F2_PL_DEPT_ROOM_T.PRJDPT_IK = F2_PL_PRJCT_DEPT_T.PRJDPT_IK

Thanks in advance!
eric

Report message to a moderator

Re: Row counter on multi-table view or query [message #39941 is a reply to message #39940] Mon, 26 August 2002 16:44 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Are you sure you can't use rownum?

select rownum rnum, x.ename, x.dname
from (select emp.ename, dept.dname
from emp, dept
where emp.deptno=dept.deptno
order by dname, ename desc) x;

You could just use inline views - or use a decode on the order by to make it dynamic.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1200189296847

Report message to a moderator

Re: Row counter on multi-table view or query [message #39948 is a reply to message #39940] Tue, 27 August 2002 07:32 Go to previous message
Eric
Messages: 78
Registered: April 1999
Member
Andrew, you cannot manipulate the rownum under various order by criteria. The rownum is generated as a result of the record retrieval process and does not change based on an order by statement - that is my problem - I need the result set to have a row counter under various order by fields.
The inline view does not seem to work either, I suppose I will have to enclose the statement inside of a loop. This seems so process intensive - yet I feel I have no other solution.

Report message to a moderator

Previous Topic: how to get information from email into SQL table
Next Topic: Top two maximun dates -Urgent
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 09:47:01 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.