This content has been marked as final. Show 4 replies
Sure, that's possible.
The ROWNUM pseudo-column is assigned as the CONNECT BY clause (including ORDER SIBLINGS BY) is being applied, so you can use ROWNUM to capture the hierarchical order.
I don't have a copy of your table, so I'll use scott.emp to illustrate:
SELECT ename, empno, mgr , ROWNUM AS r_num FROM scott.emp START WITH mgr IS NULL CONNECT BY mgr = PRIOR EMPNO ORDER SIBLINGS BY ename ;
The default CONNECT BY ordering guarantees that (for example) all of BLAKEs descendants will come after BLAKE and before anyone who is not a descendant of BLAKE. However, it says nothing about whether BLAKE will come before JONES, or vice-versa. If that's important, use ORDER SIBLINGS BY.
ENAME EMPNO MGR R_NUM ---------- ----- ----- ---------- KING 7839 1 BLAKE 7698 7839 2 ALLEN 7499 7698 3 JAMES 7900 7698 4 MARTIN 7654 7698 5 TURNER 7844 7698 6 WARD 7521 7698 7 CLARK 7782 7839 8 MILLER 7934 7782 9 JONES 7566 7839 10 FORD 7902 7566 11 SMITH 7369 7902 12 SCOTT 7788 7566 13 ADAMS 7876 7788 14
To store those numbers in your table, do the query above in a MERGE statement.
I hope this answers your question.
If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
If you're asking about a DML statement, such as MERGE, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
Explain, using specific examples, how you get those results from that data.
Always say which version of Oracle you're using.
Edited by: Frank Kulash on Feb 7, 2012 11:46 AM
user11155958 wrote:With an UPDATE statement, you'd need to identify the correct ROWNUM for each individual row, one at a time. It's certainly opossible, but it's very inefficient. If ypu assign all the ROWNUMs at once, the coding is simpler as well as faster.
so I need to use a merge statement as opposed to an update statement?
could you show me that?Sure; I'm just waiting for the CREATE TABLE and INSERT statements for the original data (that is, with NULL transid; please include the PRIMARY KEY constraint), the desired results, and the Oracle version.