Sreeja, August 13, 2001 - 6:52 am UTC
The answer is not useful becuase only for using order by clause in Select, we cannot change the database from 8i to 9i
Does this work for sorting numeric data?
Leo J. Hart IV, October 16, 2001 - 4:34 pm UTC
Does this example work for sorting numeric data? It doesn't appear to. If you wanted to sort on a field called order_number which consisted of numbers:
1000
30
203
80
It would return it in this order:
1000
203
30
80
Which is not what we want, right?
I don't have a better solution. I was trying to follow one of the examples that used index, but I couldn't get any of those to work either
October 16, 2001 - 8:17 pm UTC
If you order by a number column, it'll return the data sorted in numeric sort order.
If you have a STRING column with "numeric looking" data in it -- it'll return it sorted as STRING data -- if you want it sorted as "numbers" (but then you should have used a number column in the first place) you can always order by to_number(that_column) to get it sorted as a number.
Does this work for sorting numeric data PT- 2?
Leo J. Hart IV, October 17, 2001 - 7:42 am UTC
I appreciate your response. But in the example you are showing, aren't you concatenating a bunch of strings together to create a column to set order upon? My point it that this won't work if a person tries to use your concatenation technique on values that should be interpretted as numbers. Once you concatenate, the numbers can only be interpretted as a string and thus the order will be different than what's desired. Am I wrong? I certainly could be missing something.
Thanks!
October 17, 2001 - 8:46 am UTC
Just order by whatever you want.
ops$tkyte@ORA9I.WORLD> select lpad('*', level, '*' ) || ename ename, sal,
2 sys_connect_by_path( ename, '/' ) path
3 from emp
4 start with mgr is null
5 connect by prior empno = mgr
6 order SIBLINGS by SAL DESC
7 /
ENAME SAL PATH
--------------- ---------- -------------------------
*KING 5000 /KING
**JONES 2975 /KING/JONES
***SCOTT 3000 /KING/JONES/SCOTT
****ADAMS 1100 /KING/JONES/SCOTT/ADAMS
***FORD 3000 /KING/JONES/FORD
****SMITH 800 /KING/JONES/FORD/SMITH
**BLAKE 2850 /KING/BLAKE
***ALLEN 1600 /KING/BLAKE/ALLEN
***TURNER 1500 /KING/BLAKE/TURNER
***WARD 1250 /KING/BLAKE/WARD
***MARTIN 1250 /KING/BLAKE/MARTIN
***JAMES 950 /KING/BLAKE/JAMES
**CLARK 2450 /KING/CLARK
***MILLER 1300 /KING/CLARK/MILLER
14 rows selected.
It'll do it "right" as I said. in my example, I used the "rpad()" to empphasize the HIERARCHY. It is not relevant in the real world.
Sorry!
Leo J. Hart IV, October 17, 2001 - 9:03 am UTC
I'm sorry Tom, I was completely mixing this article up with a different article posted that used concatenation. Unfortunately, I can't use this method which WILL work because I don't have 9i and the index method doesn't seem to work either, I'm guessing because I have an index on the PK as well as the one we're creating here. Sorry for the trouble! I'm not sure how I'll get around my problem.
A bug
Mikito Harakiri, October 17, 2001 - 2:00 pm UTC
Well, "order SIBLINGS by" works in trivial cases, but any attempt of doing anything more sophisticated fails. In the example below the last 2 queries differ in a line containing "order SIBLINGS by" only, but the result set in the last case is completely wrong:
SQL> explain plan set statement_id='1'
for select 1 from dual;
Explained.
SQL> explain plan set statement_id='2' for
2 SELECT lpad(' ',level-1)||operation||' '||options||' '||
3 object_name "Plan", cardinality
4 FROM plan_table
5 CONNECT BY prior id = parent_id
6 AND prior statement_id = statement_id
7 START WITH id = 0
8 ;
Explained.
SQL> SELECT statement_id, lpad(' ',level-1)||operation||' '||options||' '||
2 object_name "Plan", cost
3 FROM plan_table
4 CONNECT BY prior id = parent_id
5 AND prior statement_id = statement_id
6 START WITH id = 0
7 ;
SU Plan COST
-- ---------------------------------------- ----------
1 SELECT STATEMENT
1 TABLE ACCESS FULL DUAL
2 SELECT STATEMENT 1
2 CONNECT BY WITH FILTERING
2 NESTED LOOPS
2 TABLE ACCESS FULL PLAN_TABLE 1
2 TABLE ACCESS BY USER ROWID PLAN_TABLE
2 HASH JOIN
2 CONNECT BY PUMP
2 TABLE ACCESS FULL PLAN_TABLE 1
10 rows selected.
SQL> SELECT statement_id, lpad(' ',level-1)||operation||' '||options||' '||
2 object_name "Plan", cost
3 FROM plan_table
4 CONNECT BY prior id = parent_id
5 AND prior statement_id = statement_id
6 START WITH id = 0
7 order SIBLINGS by COST DESC
8 ;
SU Plan COST
-- ---------------------------------------- ----------
1 SELECT STATEMENT
2 SELECT STATEMENT 1
October 17, 2001 - 2:40 pm UTC
I confirmed and reproduced with:
drop table t;
create table t ( id int, pid int, ppid int );
insert into t values ( 1, null, 1);
insert into t values ( 2, 1, 1);
select t.*, level
from t
connect by prior id = pid
and prior ppid = ppid
start with pid is null
/
select t.*, level
from t
connect by prior id = pid
and prior ppid = ppid
start with pid is null
order siblings by pid desc
/
it is now bug 2059318. It requires
o more then one clause in the connect by
o the desc order by
to reproduce.
Pooja, April 07, 2003 - 7:48 pm UTC
Do not have to read tons of documentation to learn this. Instead, your website explained that hierarchial orderering is not possible in 8i. May be, we could achieve it with index or some thing.
I just tried using order by in connect by clause. There was no error when I was exceuting the sql. I am not sure if order by is really ignored by db. In my scenario, I happened to get the correct results. I am not sure if it was just coincidence or if it really works?
April 07, 2003 - 8:05 pm UTC
it is not ignored, it just destroyed the hierarchy you worked to build is all.
the entire set is sorted, not level by level but the entire set was sorted.
A reader, May 16, 2003 - 5:21 pm UTC
I am running the following query using Oracle 8.1.7
SELECT LEVEL, empno, ename, mgr
FROM emp CONNECT BY PRIOR empno = mgr;
How to order by last name within the level?
May 16, 2003 - 6:25 pm UTC
this is achieved by upgrading to 9i which supports an order siblings by clause.
A reader, May 16, 2003 - 6:41 pm UTC
Thanks for your comments, it is little difficult to upgrade to 9i at this moment.
Sorting the data at client side is going to be costly. Any dirty trick for implementing at SQL level using Oracle 8.1.7?
May 16, 2003 - 6:45 pm UTC
see the link above - part of the original answer.
Requirement
Jignesh Karia, May 22, 2003 - 8:30 am UTC
Please go throught the script :
CREATE TABLE MY_LEVEL1 (
ORIG_RECP VARCHAR2 (10),
ORIG_AMT NUMBER,
REF_RECP VARCHAR2 (10),
REF_AMT NUMBER,
PARTY VARCHAR2 (6) ) ;
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'100', 10000, NULL, 500, 'A0001');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'110', 300, '100', 100, 'A0001');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'120', 200, '110', 50, 'A0001');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'130', 100, '120', 30, 'A0001');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'300', 10000, NULL, 500, 'A0001');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'310', 300, '300', 100, 'A0001');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'320', 200, '310', 50, 'A0001');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'330', 100, '320', 30, 'A0001');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'100', 10000, NULL, 500, 'A0003');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'110', 300, '100', 100, 'A0003');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'120', 200, '110', 50, 'A0003');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'130', 100, '120', 30, 'A0003');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'100', 10000, NULL, 500, 'A0004');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'110', 300, '100', 100, 'A0004');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'120', 200, '110', 50, 'A0004');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'130', 100, '120', 30, 'A0004');
INSERT INTO MY_LEVEL1 ( ORIG_RECP, ORIG_AMT, REF_RECP, REF_AMT,
PARTY ) VALUES (
'130', 100, '120', 30, 'A0004');
select level x,
party, orig_recp, ref_recp, orig_amt, ref_amt
from my_level1
where party between 'A0001' AND 'A0003'
connect by
prior orig_recp = ref_recp and
prior party = party
start with ref_recp is null
1 A0001 100 10000 500
2 A0001 110 100 300 100
3 A0001 120 110 200 50
4 A0001 130 120 100 30
1 A0001 300 10000 500
2 A0001 310 300 300 100
3 A0001 320 310 200 50
4 A0001 330 320 100 30
1 A0003 100 10000 500
2 A0003 110 100 300 100
3 A0003 120 110 200 50
4 A0003 130 120 100 30
Clients desired output :
1 A0001 100 10000 500
4 A0001 130 120 100 30
1 A0001 300 10000 500
4 A0001 330 320 100 30
1 A0003 100 10000 500
4 A0003 130 120 100 30
i.e. clients requirement is that they want first and last row per level. We have tried a lot but not got any appropriate solution for this.
Please provide some solution for above query asap.
May 23, 2003 - 8:17 am UTC
well, if the hierarchy is always 4 levels deep, it is pretty simple -- where level in (1,4)
Thanks for your reply but...
Jignesh Karia, May 23, 2003 - 9:05 am UTC
Sorry for not giving proper explanation for my query.
In this query, it will all be dependent on user. The business functionality is, the user is booking one bill, against he gets payment in parts, every time of part payment new receipt will be generated and the reference receipt will be updated. Now in this case the level are unknown. I.e. the user can get the payment in full first time or it may go to n number of times.
Now the requirement is that the user is looking for the first voucher and the last voucher in which he will get the original receipt and the balance amount in last voucher.
I hope you will help me out in this case
Thanks in advance.
May 23, 2003 - 10:00 am UTC
ps$tkyte@ORA920> select party, orig_recp, ref_recp, orig_amt, ref_amt,
2 rtrim(substr( data, 6, 10 )) orig_recp_2,
3 rtrim(substr( data, 16, 10 )) ref_recp_2,
4 to_number(substr( data, 26, 14 )) orig_amt_2,
5 to_number(substr( data, 40, 14 )) ref_amt_2
6 from (
7 select party, orig_recp, ref_recp, orig_amt, ref_amt,
8 (select max( to_char(level,'fm00000') ||
9 nvl(rpad(orig_recp,10),rpad(' ',10)) ||
10 nvl(rpad(ref_recp,10),rpad(' ',10)) ||
11 to_char(nvl(orig_amt,0),'0000000000.00') ||
12 to_char(nvl(ref_amt,0), '0000000000.00' ) )
13 from my_level1 t2
14 start with party = my_level1.party and ref_recp = my_level1.orig_recp
15 connect by prior orig_recp = ref_recp and prior party = party ) data
16 from my_level1
17 where party between 'A0001' and 'A0003'
18 and ref_recp is null
19 )
20 /
PARTY ORIG_RECP REF_RECP ORIG_AMT REF_AMT ORIG_RECP_ REF_RECP_2 ORIG_AMT_2 REF_AMT_2
------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
A0001 100 10000 500 130 120 100 30
A0001 300 10000 500 330 320 100 30
A0003 100 10000 500 130 120 100 30
Great Solution
jignesh karia, May 25, 2003 - 1:25 pm UTC
Thanks a Ton. . .
You are simply greate. .
waiting for your next book. . .
jignesh
Can we access all elements of a sys_connect_by_path?
Paul, June 08, 2004 - 3:15 pm UTC
I want to take
ops$tkyte@ORA9I.WORLD> select lpad('*', level, '*' ) || ename ename, sal,
2 sys_connect_by_path( ename, '/' ) path
3 from emp
4 start with mgr is null
5 connect by prior empno = mgr
6 order SIBLINGS by SAL DESC
7 /
ENAME SAL PATH
--------------- ---------- -------------------------
*KING 5000 /KING
**JONES 2975 /KING/JONES
***SCOTT 3000 /KING/JONES/SCOTT
****ADAMS 1100 /KING/JONES/SCOTT/ADAMS
***FORD 3000 /KING/JONES/FORD
****SMITH 800 /KING/JONES/FORD/SMITH
**BLAKE 2850 /KING/BLAKE
***ALLEN 1600 /KING/BLAKE/ALLEN
***TURNER 1500 /KING/BLAKE/TURNER
***WARD 1250 /KING/BLAKE/WARD
***MARTIN 1250 /KING/BLAKE/MARTIN
***JAMES 950 /KING/BLAKE/JAMES
**CLARK 2450 /KING/CLARK
***MILLER 1300 /KING/CLARK/MILLER
and turn it into a table like this
lvl1 lvl2 lvl3 lvl4 lvl5
KING null null null null
KING JONES null null null
KING JONES SCOTT null null
KING JONES SCOTT ADAMS null
[...]
For the "why" we are trying to create dimensions from a relatively loose heirarchy (ie: it isn't always city->state->country sometimes it only makes sense as lvl4->lvl3->lvl2->lvl1)
June 08, 2004 - 3:43 pm UTC
do you have a fixed depth to this hierarchy -- a reasonable maximum?
More info.
Paul, June 09, 2004 - 9:04 am UTC
I would imagine it would be no bigger than 10 levels.
Most of them would be in the 5 to 6 range.
(company, division, unit, project, activity) type information.
I have already played with decode and level to get a sparse pivot like this
1 select decode( lvl, 1, ename) lvl1,
2 decode( lvl, 2, ename ) lvl2,
3 decode( lvl, 2, ename ) lvl2,
4 decode( lvl, 3, ename ) lvl3,
5 decode( lvl, 4, ename) lvl4
6 from (
7 select level lvl,ename
8 from emp
9 start with mgr is null
10* connect by prior empno = mgr)
SQL> /
KING
JONES JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK CLARK
MILLER
I just need to figure out if/how I can fill it in.
June 09, 2004 - 9:40 am UTC
scott@ORA9IR2> select empno,
2 substr( path, instr( path, '/', 1, 1 )+1, instr( path, '/', 1, 2 )-instr(path, '/', 1, 1 )-1 ) c,
3 substr( path, instr( path, '/', 1, 2 )+1, instr( path, '/', 1, 3 )-instr(path, '/', 1, 2 )-1 ) c,
4 substr( path, instr( path, '/', 1, 3 )+1, instr( path, '/', 1, 4 )-instr(path, '/', 1, 3 )-1 ) c,
5 substr( path, instr( path, '/', 1, 4 )+1, instr( path, '/', 1, 5 )-instr(path, '/', 1, 4 )-1 ) c,
6 substr( path, instr( path, '/', 1, 5 )+1, instr( path, '/', 1, 6 )-instr(path, '/', 1, 5 )-1 ) c,
7 substr( path, instr( path, '/', 1, 6 )+1, instr( path, '/', 1, 7 )-instr(path, '/', 1, 6 )-1 ) c,
8 substr( path, instr( path, '/', 1, 7 )+1, instr( path, '/', 1, 8 )-instr(path, '/', 1, 7 )-1 ) c,
9 substr( path, instr( path, '/', 1, 8 )+1, instr( path, '/', 1, 9 )-instr(path, '/', 1, 8 )-1 ) c,
10 substr( path, instr( path, '/', 1, 9 )+1, instr( path, '/', 1, 10)-instr(path, '/', 1, 9 )-1 ) c,
11 substr( path, instr( path, '/', 1, 10)+1, instr( path, '/', 1, 11)-instr(path, '/', 1, 10)-1 ) c
12 from (
13 select empno, sys_connect_by_path( ename, '/' ) || '/' path
14 from emp
15 start with mgr is null
16 connect by prior empno = mgr
17 )
18 /
EMPNO C C C C C C C C C C
---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
7839 KING
7566 KING JONES
7788 KING JONES SCOTT
7876 KING JONES SCOTT ADAMS
7902 KING JONES FORD
7369 KING JONES FORD SMITH
7698 KING BLAKE
7499 KING BLAKE ALLEN
7521 KING BLAKE WARD
7654 KING BLAKE MARTIN
7844 KING BLAKE TURNER
7900 KING BLAKE JAMES
7782 KING CLARK
7934 KING CLARK MILLER
14 rows selected.
Thanks Tom
Paul, June 09, 2004 - 10:00 am UTC
I was thinking about some of the string functions
I was not sure if there were another way.
This is extremely helpful, Thanks again
Deriving a list of hierarchies
Steven Healey, June 09, 2004 - 3:07 pm UTC
Going back to the original example:
SQL> select lpad('*', level, '*' ) || ename ename,
2 sys_connect_by_path( ename, '/' ) path
3 from emp
4 start with mgr is null
5 connect by prior empno = mgr
6 order SIBLINGS by ename DESC;
ENAME PATH
-------------------- ----------------------------------
*KING /KING
**JONES /KING/JONES
***SCOTT /KING/JONES/SCOTT
****ADAMS /KING/JONES/SCOTT/ADAMS
***FORD /KING/JONES/FORD
****SMITH /KING/JONES/FORD/SMITH
**CLARK /KING/CLARK
***MILLER /KING/CLARK/MILLER
**BLAKE /KING/BLAKE
***WARD /KING/BLAKE/WARD
***TURNER /KING/BLAKE/TURNER
***MARTIN /KING/BLAKE/MARTIN
***JAMES /KING/BLAKE/JAMES
***ALLEN /KING/BLAKE/ALLEN
14 rows selected.
If I have a table of people for whom I would like to capture the hierarchies, such as:
drop table big_boys;
create table big_boys (empno number(4) not null);
insert into big_boys (empno) values (7839);
insert into big_boys (empno) values (7788);
SQL> select empno "BIG_BOY"
2 from big_boys;
BIG_BOY
----------
7839
7788
Such that I would like to get
BIG_BOY ENAME PATH
------- --------------- ------------------------
7839 *JONES /KING/JONES
7839 **SCOTT /KING/JONES/SCOTT
7839 ***ADAMS /KING/JONES/SCOTT/ADAMS
7839 **FORD /KING/JONES/FORD
7839 ***SMITH /KING/JONES/FORD/SMITH
7839 *CLARK /KING/CLARK
7839 **MILLER /KING/CLARK/MILLER
7839 *BLAKE /KING/BLAKE
7839 **WARD /KING/BLAKE/WARD
7839 **TURNER /KING/BLAKE/TURNER
7839 **MARTIN /KING/BLAKE/MARTIN
7839 **JAMES /KING/BLAKE/JAMES
7839 **ALLEN /KING/BLAKE/ALLEN
7788 *ADAMS /SCOTT/ADAMS
^^^^
is there a way to do this using SQL (or failing that PL/SQL)? I don't care if the listing includes the parent or not, but I have to produce a total list of the trees for all the starting points and I do not have a single starting point flag such as the null MGR.
Seems easy but I have been trying various approaches all afternoon with no success. Thanks.
sPh
June 09, 2004 - 4:18 pm UTC
sys_connect_by_path gives you that ability -- just like each row has "king/....", each row could have 7839/......
you would just start with empno in ( select * from big_boys )
easy enough to substr out.
In 10g there is a function:
ops$tkyte@ORA10G> select rpad('*',2*level,'*')||ename empname,
2 sys_connect_by_path(ename,'/') scbp,<b>
3 connect_by_root empno "root empno",</b>
4 connect_by_isleaf "is leaf"
5 from emp
6 start with ename in ( 'CLARK', 'BLAKE', 'JONES' )
7 connect by prior empno = mgr
8 /
EMPNAME SCBP root empno is leaf
--------------- ----------------------- ---------- ----------
**JONES /JONES 7566 0
****SCOTT /JONES/SCOTT 7566 0
******ADAMS /JONES/SCOTT/ADAMS 7566 1
****FORD /JONES/FORD 7566 0
******SMITH /JONES/FORD/SMITH 7566 1
**BLAKE /BLAKE 7698 0
****ALLEN /BLAKE/ALLEN 7698 1
****WARD /BLAKE/WARD 7698 1
****MARTIN /BLAKE/MARTIN 7698 1
****TURNER /BLAKE/TURNER 7698 1
****JAMES /BLAKE/JAMES 7698 1
**CLARK /CLARK 7782 0
****MILLER /CLARK/MILLER 7782 1
13 rows selected.
as well.
How will it work in this case?
Lalit K, June 18, 2004 - 12:06 pm UTC
Hi Tom,
The "LEVEL","CONNECT BY" and "SIBLINGS" works great in Oracle 9i. But I have a doubt on how to use them in a situation where a child can have multiple parents. Really appreciate your comments here.
Pls see the below tree structure. I hope it will be properly indented after I submit this question.
A--
|
---B
| |
| |
| ---D
| |
| ---E
| |
| ---F
---C
|
|
---D
|
---X
|
|
---Y
If you notice here, "D" is my shared entity having two parents B & C. How can I get this kind of tree from the below table
structure.
CREATE TABLE ENTITY_DETAIL (
ENTITY_ID NUMBER(10) NOT NULL,
ENTITY_NAME VARCHAR2(10) NOT NULL,
CONSTRAINT XPKENTITY_DETAIL PRIMARY KEY (ENTITY_ID)
);
INSERT INTO entity_detail VALUES(1,'A');
INSERT INTO entity_detail VALUES(2,'B');
INSERT INTO entity_detail VALUES(3,'C');
INSERT INTO entity_detail VALUES(4,'D');
INSERT INTO entity_detail VALUES(5,'E');
INSERT INTO entity_detail VALUES(6,'F');
INSERT INTO entity_detail VALUES(7,'X');
INSERT INTO entity_detail VALUES(8,'Y');
CREATE TABLE PARENT_CHILD_ENTITY (
ENTITY_ID NUMBER(10) NOT NULL,
PARENT_ENTITY_ID NUMBER(10) NULL,
SORT_ORDER NUMBER(3) NULL,
CONSTRAINT PARENT_CHILD_ENTITY_FK1
FOREIGN KEY (ENTITY_ID) REFERENCES ENTITY_DETAIL
);
INSERT INTO parent_child_entity VALUES(1,0,NULL);
INSERT INTO parent_child_entity VALUES(2,1,NULL);
INSERT INTO parent_child_entity VALUES(3,1,NULL);
INSERT INTO parent_child_entity VALUES(4,2,NULL);
INSERT INTO parent_child_entity VALUES(4,3,NULL);
INSERT INTO parent_child_entity VALUES(5,4,NULL);
INSERT INTO parent_child_entity VALUES(6,4,NULL);
INSERT INTO parent_child_entity VALUES(7,4,NULL);
INSERT INTO parent_child_entity VALUES(8,4,NULL);
COMMIT;
----------------------
SELECT LEVEL, LPAD(' ', LEVEL * 2) || b.entity_name entity
FROM parent_child_entity a, entity_detail b
WHERE a.entity_id = b.entity_id
START WITH a.entity_id IN (SELECT x.entity_id
FROM parent_child_entity x
WHERE x.parent_entity_id = 0)
CONNECT BY PRIOR a.entity_id = a.parent_entity_id;
LEVEL ENTITY
1 A
2 B
3 D
4 E
4 F
4 X
4 Y
2 C
3 D
4 E
4 F
4 X
4 Y
Now if you see the output of the sql, I am getting all E,F,X,Y under A-B-D, where as I only need E & F there.
Would really appreciate your comments here.
June 18, 2004 - 12:16 pm UTC
not sure I understand.
D is either the parent of E,F,X, and Y or D is not the parent of all four.
How you get to D is not relevant, D is either their parent -- or not.
I don't see how you would distinguish between
"d as a child of B"
"d as a child of C"
"d as a parent" (eg: start with D)
or anything else. D is either a parent of 4 records or.... D is not.
More Details
Lalit K, June 18, 2004 - 1:04 pm UTC
Appreciate the quick response.
I Agree, D is parent of all 4 entities E,F,X & Y.
B & C are my two different systems
B = Water Side & C = Process side
Water side consists of B, D, E & F
Process side consists of C, D, X & Y
See the below network diagram. D is somthing which is shared in both water side and process side.
| |
B C
| |
------------------
|
D
| |
------- --------
| |
------ ------
| | | |
E F X Y
Now I want the tree output like the below tree, where I want to see the tree hierarchy based on water side and process side. And not like the output of above query where I am getting all E,F,X & Y below D
--
|
---B
| |
| |
| ---D
| |
| ---E
| |
| ---F
---C
|
|
---D
|
---X
|
|
---Y
I am not sure if the above two tables will cater this requirement. How will I know if E&F are B and X&Y are from C. Looking forward for your suggestions.
June 18, 2004 - 1:13 pm UTC
you need "two d's" to accomplish that in a strict hierarchy.
you'll need to design a different structure to hold that. a foldering hierarch is not going to work here, you'll need to think up something different.
Suggestions
Lalit K, June 18, 2004 - 1:32 pm UTC
Do you have some suggestion in which direction I should move.
Should I store every top to bottom row as a flat hierarchy and generate a tree from that flat hierarchy table?
Like this -
1 - A-B-D-E
2 - A-B-D-F
3 - A-C-D-X
4 - A-C-D-Y
using 1,2,3,4, I think I would be able to create a tree in JAVA. Looking forward for a smarter solution for doing this.
June 18, 2004 - 1:42 pm UTC
there are two D's there as far as I'm concerned.
They are not the same "D".
You are not building a tree, you are building a graph. It is different. if you put this into any sort of a tree structure, D would have 4 children -- all indistingiusable from eachother.
either they are two D's in this system or you are MISSING a layer:
| |
B C
| |
------------------
|
-----D------
| |
--DB DC-
| |
------ ------
| | | |
E F X Y
you need to think about this a little more. you would be no more successful in java with your current storage.
I have a solution
Lalit K, July 01, 2004 - 11:04 am UTC
Hi Tom,
I found a way around for this problem. Let me know what u think of it.
Below is the complex network structure I have. C, D, F, G, H, I, J are the shared entities.
A
|
---------
| |
B C---J
| |
----D----
|
---------
| |
E F
| |
----G----
|
---------
| |
H I
And I want the output like below, which will explain how the entities are shared
A
-- -- B
| |
| --D
| |
| --E
| |
| --G
| |
| |-- H
| |
| -- I
-- C
|
--D
|
--F
|
--G
|
|-- H
|
-- I
J ---- C
|
--D
|
--F
|
--G
CREATE TABLE ENTITY_DETAIL_TEMP
(
ENTITY_ID NUMBER(10) NOT NULL,
ENTITY_NAME VARCHAR2(200) NOT NULL,
ENTITY_TYPE_ID NUMBER(10),
CONFIG_ENTITY_ID NUMBER(10),
SHARE_IND CHAR(1) DEFAULT 'N',
CUST_ACCOUNT_ID NUMBER(10)
);
ALTER TABLE ENTITY_DETAIL_TEMP ADD ( CONSTRAINT XPK_ENTITY_DETAIL_TEMP PRIMARY KEY (ENTITY_ID));
INSERT INTO ENTITY_DETAIL_TEMP (entity_id,entity_name,share_ind) VALUES(1,'A','N');
INSERT INTO ENTITY_DETAIL_TEMP (entity_id,entity_name,share_ind) VALUES(2,'B','N');
INSERT INTO ENTITY_DETAIL_TEMP (entity_id,entity_name,share_ind) VALUES(3,'C','Y');
INSERT INTO ENTITY_DETAIL_TEMP (entity_id,entity_name,share_ind) VALUES(4,'D','Y');
INSERT INTO ENTITY_DETAIL_TEMP (entity_id,entity_name,share_ind) VALUES(5,'E','N');
INSERT INTO ENTITY_DETAIL_TEMP (entity_id,entity_name,share_ind) VALUES(6,'F','Y');
INSERT INTO ENTITY_DETAIL_TEMP (entity_id,entity_name,share_ind) VALUES(7,'G','Y');
INSERT INTO ENTITY_DETAIL_TEMP (entity_id,entity_name,share_ind) VALUES(8,'H','Y');
INSERT INTO ENTITY_DETAIL_TEMP (entity_id,entity_name,share_ind) VALUES(9,'I','Y');
INSERT INTO ENTITY_DETAIL_TEMP (entity_id,entity_name,share_ind) VALUES(10,'J','N');
CREATE TABLE PARENT_CHILD_ENTITY_TEMP
(
UNIQUE_ID NUMBER(10),
ENTITY_ID NUMBER(10),
PARENT_ENTITY_ID NUMBER(10),
PARENT_UNIQUE_ID NUMBER(10),
SORT_ORDER NUMBER(3)
)
ALTER TABLE PARENT_CHILD_ENTITY_TEMP ADD ( CONSTRAINT XPK_PARENT_CHILD_ENTITY_TEMP PRIMARY KEY (UNIQUE_ID));
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (1,1,0,0);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (2,2,1,1);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (3,4,2,2);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (4,5,4,3);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (5,7,5,4);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (6,8,7,5);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (7,9,7,5);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (8,3,1,1);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (9,4,3,8);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (10,6,4,9);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (11,7,6,10);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (12,8,7,11);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (13,9,7,11);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (14,10,0,0);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (15,3,10,14);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (16,4,3,15);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (17,6,4,16);
INSERT INTO PARENT_CHILD_ENTITY_TEMP(unique_id,entity_id, parent_entity_id,parent_unique_id) VALUES (18,7,6,17);
SQL> SELECT
2 LPAD(' ', LEVEL * 5) || b.entity_name tree
3 --,sys_connect_by_path(NULL||'~'||a.entity_id||'~'||b.entity_name,',') PATH
4 FROM PARENT_CHILD_ENTITY_TEMP a, ENTITY_DETAIL_TEMP b
5 WHERE a.entity_id = b.entity_id
6 --AND a.unique_id NOT IN (SELECT DISTINCT parent_unique_id FROM parent_child_entity_test)
7 START WITH a.unique_id IN (SELECT b.unique_id
8 FROM PARENT_CHILD_ENTITY_TEMP b
9 WHERE b.parent_unique_id = 0)
10 CONNECT BY PRIOR a.unique_id = a.parent_unique_id;
A
B
D
E
G
H
I
C
D
F
G
H
I
J
C
D
F
G
Appreciate your comments.
July 01, 2004 - 11:35 am UTC
if you are happy and it returns the right answer in a reasonable amount of time -- so am I.
Awesome
Ed Hilsinger, July 14, 2004 - 12:05 pm UTC
Everytime I come here I learn something new. Something I think would be very difficult you do in just a very simpl select statement.
Thank you very much
Need to Order this hierarchy
A reader, March 02, 2005 - 5:34 pm UTC
Hello Sir,
I have a parent-child set of rows. Need to order them . Please see the example and test data.
show all parent records ordered by sno but if it has a child record show those details immediately after the parent row.
The Required output will make it clear what I want I hope.
select * from testorder ORDER BY id , sno ,sub_sno
ID PARENT_ID SNO SUB_SNO MAIN_NAME SUB_NAME
---------- ---------- ---------- ---------- ------------------------- ----------
1 1 1 MASTER RUN SUB NAME 1
1 2 1 MASTER RUN SUB NAME 2
1 3 1 MASTER RUN APLHA
1 4 1 MASTER RUN BETA
2 1 1 1 CHILD SUB NAME 2 SUB NAME X
2 1 2 1 CHILD SUB NAME 2 SUB NAME Y
3 1 1 1 CHILD BETA SUB NAME Z
3 1 1 2 CHILD BETA SUB NAME P
Required output:
ID PARENT_ID SNO SUB_SNO MAIN_NAME SUB_NAME
---------- ---------- ---------- ---------- ------------------------- ----------
1 1 1 MASTER RUN SUB NAME 1
1 2 1 MASTER RUN SUB NAME 2
2 1 1 1 CHILD SUB NAME 2 SUB NAME X
2 1 2 1 CHILD SUB NAME 2 SUB NAME Y
1 3 1 MASTER RUN APLHA
1 4 1 MASTER RUN BETA
3 1 1 1 CHILD BETA SUB NAME Z
3 1 1 2 CHILD BETA SUB NAME P
CREATE TABLE TESTORDER
(
ID NUMBER,
PARENT_ID NUMBER,
SNO NUMBER,
SUB_SNO NUMBER,
MAIN_NAME VARCHAR2(25 BYTE),
SUB_NAME VARCHAR2(25 BYTE)
);
INSERT INTO TESTORDER ( ID, PARENT_ID, SNO, SUB_SNO, MAIN_NAME,
SUB_NAME ) VALUES (
1, NULL, 1, 1, 'MASTER RUN', 'SUB NAME 1');
INSERT INTO TESTORDER ( ID, PARENT_ID, SNO, SUB_SNO, MAIN_NAME,
SUB_NAME ) VALUES (
1, NULL, 2, 1, 'MASTER RUN', 'SUB NAME 2');
INSERT INTO TESTORDER ( ID, PARENT_ID, SNO, SUB_SNO, MAIN_NAME,
SUB_NAME ) VALUES (
3, 1, 1, 2, 'CHILD BETA', 'SUB NAME P');
INSERT INTO TESTORDER ( ID, PARENT_ID, SNO, SUB_SNO, MAIN_NAME,
SUB_NAME ) VALUES (
1, NULL, 3, 1, 'MASTER RUN', 'APLHA');
INSERT INTO TESTORDER ( ID, PARENT_ID, SNO, SUB_SNO, MAIN_NAME,
SUB_NAME ) VALUES (
1, NULL, 4, 1, 'MASTER RUN', 'BETA');
INSERT INTO TESTORDER ( ID, PARENT_ID, SNO, SUB_SNO, MAIN_NAME,
SUB_NAME ) VALUES (
2, 1, 1, 1, 'CHILD SUB NAME 2', 'SUB NAME X');
INSERT INTO TESTORDER ( ID, PARENT_ID, SNO, SUB_SNO, MAIN_NAME,
SUB_NAME ) VALUES (
2, 1, 2, 1, 'CHILD SUB NAME 2', 'SUB NAME Y');
INSERT INTO TESTORDER ( ID, PARENT_ID, SNO, SUB_SNO, MAIN_NAME,
SUB_NAME ) VALUES (
3, 1, 1, 1, 'CHILD BETA', 'SUB NAME Z');
COMMIT;
March 02, 2005 - 5:48 pm UTC
ID PARENT_ID SNO SUB_SNO MAIN_NAME SUB_NAME
---------- ---------- ---------- ---------- ------------------------- ----------
1 1 1 MASTER RUN SUB NAME 1
1 2 1 MASTER RUN SUB NAME 2
2 1 1 1 CHILD SUB NAME 2 SUB NAME X
2 1 2 1 CHILD SUB NAME 2 SUB NAME Y
is that right? why not
ID PARENT_ID SNO SUB_SNO MAIN_NAME SUB_NAME
---------- ---------- ---------- ---------- ------------------------- ----------
1 1 1 MASTER RUN SUB NAME 1
2 1 1 1 CHILD SUB NAME 2 SUB NAME X
1 2 1 MASTER RUN SUB NAME 2
2 1 2 1 CHILD SUB NAME 2 SUB NAME Y
More info :
A reader, March 02, 2005 - 6:07 pm UTC
ID PARENT_ID SNO SUB_SNO MAIN_NAME SUB_NAME
---------- ---------- ---------- ---------- ------------------------- ----------
1 1 1 MASTER RUN SUB NAME 1
1 2 1 MASTER RUN SUB NAME 2
2 1 1 1 CHILD SUB NAME 2 SUB NAME X
2 1 2 1 CHILD SUB NAME 2 SUB NAME Y
The above is what I am trying to achieve .
The below is not right because.
the process with the MAIN_NAME = MASTER RUN AND SUB_NAME = SUB NAME 2 actually initiates
the process with the MAIN_NAME = CHILD SUB NAME 2.
Basically I want all related processes to be shown together as show in the main required output.
"If a child process is called then the next row should show that child process details."
ID PARENT_ID SNO SUB_SNO MAIN_NAME SUB_NAME
---------- ---------- ---------- ---------- ------------------------- ----------
1 1 1 MASTER RUN SUB NAME 1
2 1 1 1 CHILD SUB NAME 2 SUB NAME X
1 2 1 MASTER RUN SUB NAME 2
2 1 2 1 CHILD SUB NAME 2 SUB NAME Y
March 02, 2005 - 6:59 pm UTC
The below is not right because.
the process with the MAIN_NAME = MASTER RUN AND SUB_NAME = SUB NAME 2 actually
initiates
the process with the MAIN_NAME = CHILD SUB NAME 2.
and what told you that....
(it is most strange to have a 'hierarchy' in a flat table.)
More info
A reader, March 02, 2005 - 7:35 pm UTC
With respect to "The below is not right because.
the process with the MAIN_NAME = MASTER RUN AND SUB_NAME = SUB NAME 2 actually
initiates
the process with the MAIN_NAME = CHILD SUB NAME 2.
and what told you that...."
This is known by the string VALUE = SUB NAME 2 in the column MAIN_NAME whose value is "CHILD SUB NAME 2"
So having a column value of SUB_NAME( SUB NAME 2) in the MAIN_NAME (CHILD SUB NAME 2) column tells us that it is a child process plus you will see its
parent_id is not null and = 1
So any clues/soln to getting the required output.
Thanx
March 02, 2005 - 7:39 pm UTC
i don't think so, looks very message -- that the names are substrings and all leads to a host of other questions.
This data does not look very predicably orderable to me, I don't see it. You would need to lay out all of the rules for sorting -- and if you did that, you might be able to do it yourself -- but there are too many hidden things in here.
This isn't really a hierarchy - if you are not firmly into using this strucutre, consider using a true hirearchy with relationships (parent child pointers, use of the connect by clause and so on)
not going to solve this here in a followup i don't think
OK
A reader, March 02, 2005 - 8:10 pm UTC
I will wait to submit this as a new question. Thanx for your help.
Basically I just want to list all records such that if a record has child records list those details immediatley else just list the childless records.
I tried using connect by and order by siblings.
It just lists all the parent_id = null records then lists the all the child records.
I wanted parent records and then the related child records if any.
Thanks.
Dependency tree for views
Paul, April 12, 2005 - 5:16 pm UTC
Tom,
How can I get a similar tree for a given view name? If the given view is based on another view, that too should be listed. Say something like drilling down into views upto the bottom table (in the below list, views are prefixed with 'v' and tables with 't').
Alternate 1:
View Dependent View/Table
---------- --------------------
v9 v8
t9
v44
v8 v11
t22
v44 t7
t8
v11 t3
Alternate 2:
View Dependent View/Table
---------- --------------------
v9 v8, t9, v44
v8 v11, t22
v44 t7, t8
v11 t3
If you feel the above data can be displayed in more better way, please do so.
Thanks again for all your help!
Regards!!!
April 13, 2005 - 8:58 am UTC
this'll get you started, right now assumed everything in single schema, dba_dependencies exists as well.
ops$tkyte@ORA9IR2> create table t1 ( x int );
Table created.
ops$tkyte@ORA9IR2> create table t2 ( y int );
Table created.
ops$tkyte@ORA9IR2> create table t3 ( z int );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v1 as select * from t1;
View created.
ops$tkyte@ORA9IR2> create or replace view v2 as select * from v1, t2;
View created.
ops$tkyte@ORA9IR2> create or replace view v3 as select v1.x, v2.y, t3.z from v1, v2, t3;
View created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> with ud
2 as
3 (select name, type, referenced_name , referenced_type , referenced_owner
4 from user_dependencies
5 )
6 select rpad('*',2*level,'*')||referenced_name name, referenced_type type
7 from ud
8 start with name = 'V3' and type = 'VIEW'
9 connect by prior referenced_name = name and prior referenced_type = type and prior referenced_owner = user
10 /
NAME TYPE
------------------------------ -----------------
**T3 TABLE
**V1 VIEW
****T1 TABLE
**V2 VIEW
****T2 TABLE
****V1 VIEW
******T1 TABLE
7 rows selected.
WOW!!! - 100 Stars :)
A reader, April 13, 2005 - 3:27 pm UTC
Tom,
Can I rate the response as 100 on the scale of 1 to 5? :-) Amazing .. I was working on this query for long time and now this sql works great. Sweet!
By the way, is there any reason why you used "with ud as" instead of selecting directly from user_dependencies view?
Once again, thanks a bunch for the solution!
April 13, 2005 - 4:19 pm UTC
the dependencies views can be quite slow -- and in a connect by, I could imagine very slow.
So, i was hoping the with might materiize it .. if not, I would add "rownum" to the select list of the with query and that should do it for sure.
A reader, April 14, 2005 - 1:38 pm UTC
Yes, when I run this query with some of my views, its very slow. But how can adding rownum help?
April 14, 2005 - 1:42 pm UTC
my materializing it (dba dependencies) once and reusing that over and over.
Multiple Parents
Mark S., April 19, 2005 - 9:18 am UTC
How would a multiple parent situation be handled? For example A and B are parents. C is child of A, and D is child of B. E is a child of both A & B. How would all the relationships be handled in this scenario?
Thanks in advance
April 19, 2005 - 9:29 am UTC
they would be there? no problems.
But is it possible/how in 9i?
Adam Wesselink, April 19, 2005 - 3:39 pm UTC
In your followup to Steven Healey, you used the 10g function connect_by_root:
In 10g there is a function:
ops$tkyte@ORA10G> select rpad('*',2*level,'*')||ename empname,
2 sys_connect_by_path(ename,'/') scbp,
3 connect_by_root empno "root empno",
4 connect_by_isleaf "is leaf"
5 from emp
6 start with ename in ( 'CLARK', 'BLAKE', 'JONES' )
7 connect by prior empno = mgr
8 /
I need to do the same thing but in 9i (ie, I have a child n levels down, and I need to find the top level parent). How can I do this?
April 19, 2005 - 7:44 pm UTC
substr the sys_connect_by_path......
date in connect by
oracleo, May 17, 2005 - 2:52 am UTC
can we use a date column in connect by clause. I actually do to_char of that column as to_char(dateColumn,'dd-mon-yyyy'). but it doesn't seem to work and returns only the parent row. any clues. BTW i am using 9i.
Thanks
May 17, 2005 - 8:35 am UTC
need example. you probably should NOT be using to_char() at all, but give us an exmaple. create a simple two row table with a hierarchy to demonstrate what you are doing.
connect_by_isleaf equivalent in 9i?
A reader, May 19, 2005 - 1:12 pm UTC
Tom,
You mentioned connect_by_isleaf in 10g, which is cool.
How can I do the equivalent of connect_by_isleaf in 9i?
May 19, 2005 - 1:59 pm UTC
you would have to use a scalar subquery.
ops$ora9ir2@ORA9IR2> select rpad('*',2*level,'*')||ename name,
2 nvl( (select 0 from scott.emp e2 where e2.mgr = e.empno and rownum = 1), 1 ) is_leaf
3 from scott.emp e
4 start with mgr is null
5 connect by prior empno = mgr;
NAME IS_LEAF
--------------- ----------
**KING 0
****JONES 0
******SCOTT 0
********ADAMS 1
******FORD 0
********SMITH 1
****BLAKE 0
******ALLEN 1
******WARD 1
******MARTIN 1
******TURNER 1
******JAMES 1
****CLARK 0
******MILLER 1
14 rows selected.
Awesome
A reader, May 19, 2005 - 2:17 pm UTC
Tom,
You are the best!
I want the count of the children in the parent row
Richard Xu, May 20, 2005 - 1:51 pm UTC
I want to get the count of its children in the parent row. So I just modify your example like this:
select rpad('*',2*level,'*')||ename name,
(select count(*)
from scott.emp e2
where e2.mgr = e.empno) childrencount
from scott.emp e
start with mgr is null
connect by prior empno = mgr;
I am not sure if there is another better way to do it.
Thanks.
May 20, 2005 - 6:43 pm UTC
that works, it would depend on the total number of rows you actually retrieve as to whether i would use scalar subquery or join.
Maintaining order after connect by
Bob B, May 26, 2005 - 3:44 pm UTC
I want to retrieve the heirarchical data from a table and then, for each item in the heirarchy, display the details of that item. If this were run on a generic department, employee relationship, I would want the heirarchy of departments and underneath each department, all the employees in that department.
CREATE TABLE TMP_DEPT(
DEPT_ID NUMBER PRIMARY KEY,
PT_DEPT_ID NUMBER
);
ALTER TABLE TMP_DEPT ADD CONSTRAINT TMP_DEPT_FK FOREIGN KEY (PT_DEPT_ID) REFERENCES TMP_DEPT (DEPT_ID);
INSERT INTO TMP_DEPT VALUES( 1, NULL );
INSERT INTO TMP_DEPT VALUES( 2, NULL );
INSERT INTO TMP_DEPT VALUES( 3, 1 );
INSERT INTO TMP_DEPT VALUES( 4, 1 );
INSERT INTO TMP_DEPT VALUES( 5, 2 );
INSERT INTO TMP_DEPT VALUES( 6, 2 );
INSERT INTO TMP_DEPT VALUES( 7, 3 );
INSERT INTO TMP_DEPT VALUES( 8, 3 );
INSERT INTO TMP_DEPT VALUES( 9, 4 );
INSERT INTO TMP_DEPT VALUES( 10, 4 );
INSERT INTO TMP_DEPT VALUES( 11, 5 );
INSERT INTO TMP_DEPT VALUES( 12, 5 );
INSERT INTO TMP_DEPT VALUES( 13, 6 );
INSERT INTO TMP_DEPT VALUES( 14, 6 );
CREATE TABLE TMP_EMP AS
SELECT ROWNUM EMP_ID, DEPT_ID, DECODE( MOD( ROWNUM, 2 ), 0, 'Bob', 1, 'Joe' ) EMP_NAME
FROM TMP_DEPT;
ALTER TABLE TMP_EMP ADD CONSTRAINT TMP_EMP_PK PRIMARY KEY (EMP_ID);
ALTER TABLE TMP_EMP ADD CONSTRAINT TMP_EMP_DEPT_ID_FK FOREIGN KEY (DEPT_ID) REFERENCES TMP_DEPT (DEPT_ID);
SELECT *
FROM TMP_EMP te, (
SELECT DEPT_ID, PT_DEPT_ID, ROWNUM rn
FROM TMP_DEPT
START WITH PT_DEPT_ID IS NULL
CONNECT BY PRIOR DEPT_ID = PT_DEPT_ID
) td
WHERE td.DEPT_ID = te.DEPT_ID
ORDER BY RN, EMP_NAME
The query above works, but I don't like relying on rownum. Also, in order to use the "order siblings by" clause, the only way I can think to keep that ordering is to replace the "td" portion of the query with:
SELECT tmp_td.*, ROWNUM rn
FROM (
SELECT DEPT_ID, PT_DEPT_ID
FROM TMP_DEPT
START WITH PT_DEPT_ID IS NULL
CONNECT BY PRIOR DEPT_ID = PT_DEPT_ID
ORDER SIBLINGS BY DEPT_ID DESC
) tmp_td
If this is out of scope, I can post it as a new question.
May 26, 2005 - 7:32 pm UTC
well, rownum is going to be the easiest way to do this -- other than "not liking it", is there a technical issue?
No technical issue ...
Bob B, May 27, 2005 - 12:23 am UTC
It works fine. It just looked like there should be a better way to do it. I considered ordering it by sys_connect_by_path( field padded to fixed length ) but rownum seemed like it'd be faster and clearer (no proof one way or the other, just a hunch).
Its quite easy to sort a heirarchy "depth first" (i.e. order by LEVEL), but there is no way to do breadth first order other than using rownum.
Order Siblings By with Level is not working in 10g
A reader, March 31, 2006 - 3:22 pm UTC
Order Siblings By with Level is not working in 10g, though the same query is working on 9.2.0.4. I got the following ORA error.
'ORA-00976: LEVEL, PRIOR, or ROWNUM not allowed here'
Here is the sample query.
select sys_connect_by_path(name, '/'), level, other columns
from my_tab
where id = 1
connect by prior id = parent_id
start with name 'my node'
order siblings by level, other columns
Can you please help me to reslove this issue?
March 31, 2006 - 4:44 pm UTC
you do not order by level, that would muck it up.
Just order by the attributes - it orders the SIBILINGS preserving the levels
order by level would not make sense with "siblings"
if it accidently worked in 9ir2 - I don't know what it would have "meant" - but it would have been a bug.
connect by level
Karthikeyan Baskaran, September 18, 2009 - 5:56 am UTC
Hi Tom,
I have tried to learn about "connect by level" usage. if I give "connect by level <= 30" it is returning only one record.
I have created the table with that select script. It is has populated the 30 records. But it is not displaying in the select.
Do I want set up any parameter in the SQL* plus. Please guide me.
Scripts:SQL*Plus: Release 9.2.0.1.0 - Production on Fri Sep 18 16:12:13 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn demo1/demo1@dba3
Connected.
The select stmt is giving only one record.SQL> set feedback on;
SQL> select level x, level y
2 from dual
3 connect by level <= 30;
X Y
---------- ----------
1 1
1 row selected.I have tried with the count(*). It is showing correct value. SQL> select count(*) from dual
2 connect by level <= 30;
COUNT(*)
----------
30
1 row selected.
SQL> create table t
2 as
3 select level x, level y
4 from dual
5 connect by level <= 30;
Table created.
SQL> select count(*) from t;
COUNT(*)
----------
30
1 row selected.
SQL>Regards,
Karthi
September 18, 2009 - 12:30 pm UTC
It didn't work properly in 9i, use
with data as (select level l from dual connect by level <= 30)
select * from data;
or
select * from (select level l from dual connect by level <= 30)
it was more of a fetching issue - they saw a query against dual and said "oh, one row, stop"
ORDER SIBLINGS BY and ROWNUM
Michael Wuschek, October 23, 2009 - 8:51 am UTC
Tom,
I have a hierarchical query which should return a set of rows where each row contains a reference to the position of the parent row in the set. The siblings must be ordered by a given column.
create table hierarchy (
id number(4),
parent number(4),
sort number(4),
constraint hi_pk primary key (id) using index);
insert into hierarchy values (1,null,2);
insert into hierarchy values (2,1,1);
insert into hierarchy values (3,1,11);
insert into hierarchy values (4,3,11);
insert into hierarchy values (5,3,1);
insert into hierarchy values (6,1,111);
commit;
col path for a20
col rownum_of_parent for a6 head "Rownum|of|parent"
-- the select statement uses rownum and sys_connect_by_path for the reference to the parent:
-- the ascending version
SELECT id,
parent,
sys_connect_by_path(ROWNUM,'/') path,
rownum,
substr(sys_connect_by_path(ROWNUM,'/'),
instr(sys_connect_by_path(ROWNUM,'/') ,'/',-1,2)+1,
instr(sys_connect_by_path(ROWNUM,'/') ,'/',-1,1)
- instr(sys_connect_by_path(ROWNUM,'/') ,'/',-1,2)-1
) rownum_of_parent,
sort
FROM hierarchy
START WITH parent IS NULL
CONNECT BY PRIOR id = parent
order siblings by sort asc;
-- the descending version
SELECT id,
parent,
sys_connect_by_path(ROWNUM,'/') path,
rownum,
substr(sys_connect_by_path(ROWNUM,'/'),
instr(sys_connect_by_path(ROWNUM,'/') ,'/',-1,2)+1,
instr(sys_connect_by_path(ROWNUM,'/') ,'/',-1,1)
- instr(sys_connect_by_path(ROWNUM,'/') ,'/',-1,2)-1
) rownum_of_parent,
sort
FROM hierarchy
START WITH parent IS NULL
CONNECT BY PRIOR id = parent
order siblings by sort desc;
Both queries return correct results in 10.2.0.4. But can I rely on rownum being assigned AFTER ordering the siblings?
October 23, 2009 - 2:44 pm UTC
... But can I rely on rownum being assigned AFTER ordering the siblings? ...
no, I would not - I'm not aware of anything that says it would be so.
Row Generation
A reader, June 01, 2011 - 1:57 am UTC
CREATE TABLE SCOTT.TEST_RECORD
(
ID NUMBER(3),
MAX_ROW NUMBER(2)
);
SET DEFINE OFF;
Insert into TEST_RECORD
(ID, MAX_ROW)
Values
(1, 3);
Insert into TEST_RECORD
(ID, MAX_ROW)
Values
(2, 5);
Insert into TEST_RECORD
(ID, MAX_ROW)
Values
(3, 2);
COMMIT;
For each record generate number of rows (records) which is given in max_row. For Id 1 generate 3 records, for id 2 generate 5 records and for id 3 generate 2 records. Oracle Version 10 G and 11.2 G
June 01, 2011 - 8:45 am UTC
ops$tkyte%ORA11GR2> select id, max_row, column_value
2 from t, table( cast( multiset( select level from dual connect by level <= t.max_row) as sys.odciNumberList ) )
3 /
ID MAX_ROW COLUMN_VALUE
---------- ---------- ------------
1 3 1
1 3 2
1 3 3
2 5 1
2 5 2
2 5 3
2 5 4
2 5 5
3 2 1
3 2 2
10 rows selected.
row generation
A reader, June 01, 2011 - 9:06 am UTC
great amazing. i was trying to get by using CONNECT BY LEVEL. How is it getting value inside for t.max_row(CONNECT BY LEVEL <= t.max_row)?
June 01, 2011 - 2:34 pm UTC
table() clause does that. it is like a "self join at the row level"