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 Go to next message
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 #428332 is a reply to message #428306] Tue, 27 October 2009 23:32 Go to previous messageGo to next message
kish_ulike
Messages: 30
Registered: June 2008
Location: chennai
Member

Hi

I found query with my friends ....to ans you.

select * from dms a
where a.type = 0
and a.parentid not in (select b.dataid from dms b where b.type=0)
and exists(select 1 from dms c,dms d
where c.parentid = a.dataid
and d.type=1
and d.parentid = c.dataid)



it will give the data with parent id...
check with this if this is not your required ans...please elaborate your question?

Note:-
-----
Sorry for my poor englis Smile Smile
Re: connect by prior question [message #428342 is a reply to message #428306] Wed, 28 October 2009 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See my answer in usenet:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/ec17d0eca2bfbf56/e0bb322513248f65

Regards
Michel
Re: connect by prior question [message #428424 is a reply to message #428306] Wed, 28 October 2009 05:57 Go to previous messageGo to next message
_jum
Messages: 508
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
_jum
Messages: 508
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 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Seeing active locks & sessions (translating from Informix) (merged 3)
Next Topic: Getting data from 3 rows with three fields to 1 row with 9 fields
Goto Forum:
  


Current Time: Sun Sep 25 07:45:58 CDT 2016

Total time taken to generate the page: 0.21839 seconds