Home » SQL & PL/SQL » SQL & PL/SQL » connect by prior question (oracle 10g 10.2.1)
connect by prior question [message #428306] |
Tue, 27 October 2009 17:34  |
Mahesh P
Messages: 69 Registered: September 2004
|
Member |
|
|
Hi,
Here is the DB table:
create table dms (parentid number,dataid number,name varchar2(200),type number)
insert into dms values(1000,1001,folderA,0);
insert into dms values(1000,1002,folderB,0);
insert into dms values(1001,1003,folderC,0);
insert into dms values(1002,1004,folderD,0);
insert into dms values(1000,1005,folderE,0);
insert into dms values(1005,1006,folderF,0);
insert into dms values(1003,1011,doc1,1);
insert into dms values(1003,1012,doc2,1);
insert into dms values(1003,1013,doc3,1);
insert into dms values(1003,1014,doc4,1);
insert into dms values(1004,1021,doc1,1);
insert into dms values(1004,1022,doc2,1);
level 1 | level 2
folders | folders
-----------------------
From this table: folderA -> folderC -> 4 docs(doc1...doc4)
folderB -> folderD -> 2 docs(doc1,doc2)
folderE -> folderF (no docs)
I need to write a query to list level 1 folders (i.e. folderA,folderB) which has documents. Documents & folders were differentiated by type column in dms table. if type=0 then it is folder and if it is 1 it is a document.
I tried to use the connect by prior but cannot able to find level1 folders that has only documents.
Appreciate your help..
Mahesh
|
|
|
|
|
Re: connect by prior question [message #428424 is a reply to message #428306] |
Wed, 28 October 2009 05:57   |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
Find the matching folder/docs with CONNECT BY in a subquery and then the folder/folder in a join.
SELECT name, pname, nam
FROM
(SELECT parentid pid, dataid did, PRIOR name nam, name pname
FROM test_243
CONNECT BY (PRIOR parentid = dataid AND PRIOR TYPE = 1)), test_243
WHERE dataid = pid AND nam IS NOT NULL;
NAME PNAME NAM
folderA folderC doc1
folderA folderC doc2
folderA folderC doc3
folderA folderC doc4
folderB folderD doc1
folderB folderD doc2
|
|
|
Re: connect by prior question [message #428448 is a reply to message #428306] |
Wed, 28 October 2009 06:51   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> col first_rep format a10
SQL> col last_rep format a10
SQL> select first_rep, last_rep,
2 decode(type,
3 0, 'no doc',
4 to_char(count(*))||' docs') nb_docs
5 from ( select connect_by_root name first_rep, type,
6 decode(type,
7 0, name,
8 (select name from dms where dataid = d1.parentid))
9 last_rep
10 from dms d1
11 where connect_by_isleaf = 1
12 connect by prior dataid = parentid
13 start with parentid not in (select dataid from dms)
14 )
15 group by first_rep, last_rep, type
16 order by 1, 2
17 /
FIRST_REP LAST_REP NB_DOCS
---------- ---------- ---------------------------------------------
folderA folderC 4 docs
folderB folderD 2 docs
folderE folderF no doc
3 rows selected.
Regards
Michel
|
|
|
Re: connect by prior question [message #428468 is a reply to message #428306] |
Wed, 28 October 2009 08:07   |
Mahesh P
Messages: 69 Registered: September 2004
|
Member |
|
|
Thank you very much for your replies folks..
It looks like we need to tweak the query to work for more than 2levels..
i.e. if I have a folder structure like this:
folderA
-> FolderB
-> FolderC
-> FolderD
-> here the documents
-> FolderE
-> FolderF
-> FolderG
-> here the docs.
.....
.....
Where as folderA is the top level folder or root folder. I want to list FolderB contains so many docs, FolderE contains so nany docs/ nodocs etc...
|
|
|
Re: connect by prior question [message #428483 is a reply to message #428468] |
Wed, 28 October 2009 08:43   |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
Expand the query a litte and play aroung with the hierachical queries, f.e.:
WITH test_243 AS
(
SELECT 1000 parentid, 1001 dataid, 'folderA' name, 0 type FROM dual UNION ALL
SELECT 1000 , 1002 , 'folderB' , 0 FROM dual UNION ALL
SELECT 1000 , 1005 , 'folderE' , 0 FROM dual UNION ALL
SELECT 1001 , 1003 , 'folderC' , 0 FROM dual UNION ALL
SELECT 1002 , 1004 , 'folderD' , 0 FROM dual UNION ALL
SELECT 1003 , 1011 , 'doc1' , 1 FROM dual UNION ALL
SELECT 1003 , 1007 , 'folderC1' , 0 FROM dual UNION ALL
SELECT 1003 , 1014 , 'doc4' , 1 FROM dual UNION ALL
SELECT 1003 , 1013 , 'doc3' , 1 FROM dual UNION ALL
SELECT 1003 , 1012 , 'doc2' , 1 FROM dual UNION ALL
SELECT 1004 , 1021 , 'doc1' , 1 FROM dual UNION ALL
SELECT 1004 , 1022 , 'doc2' , 1 FROM dual UNION ALL
SELECT 1005 , 1006 , 'folderF' , 0 FROM dual UNION ALL
SELECT 1007 , 1031 , 'doc6' , 1 FROM dual)
SELECT level lv, CONNECT_BY_ROOT name rnam, name, SYS_CONNECT_BY_PATH(name,'->')
FROM test_243
WHERE PRIOR type is not null
AND type=1
CONNECT BY ((parentid = prior dataid))
ORDER BY 2, 1 desc, 3;
LV RNAM NAME SYS_CONNECT_BY_PATH(NAME,'->')
4 folderA doc6 ->folderA->folderC->folderC1->doc6
3 folderA doc1 ->folderA->folderC->doc1
3 folderA doc2 ->folderA->folderC->doc2
3 folderA doc3 ->folderA->folderC->doc3
3 folderA doc4 ->folderA->folderC->doc4
3 folderB doc1 ->folderB->folderD->doc1
3 folderB doc2 ->folderB->folderD->doc2
3 folderC doc6 ->folderC->folderC1->doc6
2 folderC doc1 ->folderC->doc1
2 folderC doc2 ->folderC->doc2
2 folderC doc3 ->folderC->doc3
2 folderC doc4 ->folderC->doc4
2 folderC1 doc6 ->folderC1->doc6
2 folderD doc1 ->folderD->doc1
2 folderD doc2 ->folderD->doc2
You can GROUP BY and COUNT the docs...
[Updated on: Wed, 28 October 2009 08:45] Report message to a moderator
|
|
|
Re: connect by prior question [message #428504 is a reply to message #428468] |
Wed, 28 October 2009 09:42  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> select decode(flag,
2 0, max(name),
3 decode(type,
4 0, max(name),
5 lpad('-> ',5*(lvl-1))||to_char(count(*))||' docs'
6 )
7 ) name
8 from (
9 select lpad('-> ',5*(level-1))||name name, sys_connect_by_path(name,'/') path,
10 type, connect_by_isleaf flag, level lvl,
11 max(decode(type,0,rownum)) over(order by rownum) rn
12 from dms d1
13 connect by prior dataid = parentid
14 start with parentid not in (select dataid from dms)
15 )
16 group by substr(path,1,instr(path,'/',-1,1)), flag, type, lvl, rn
17 order by rn, lvl
18 /
NAME
------------------------------
folderA
-> folderC
-> 4 docs
folderB
-> folderD
-> 2 docs
folderE
-> folderF
Regards
Michel
|
|
|
Goto Forum:
Current Time: Tue Feb 18 14:26:35 CST 2025
|