Re: Oracle 9 Query question...
Date: Thu, 10 Jan 2008 06:23:28 -0800 (PST)
Message-ID: <4a97d39b-857f-4e91-9a63-41378090d952@f10g2000hsf.googlegroups.com>
On Jan 10, 7:37 am, "M_at_r(o" <m.hoef..._at_lelystad.nl> wrote:
> Hello,
>
> I am no Query expert so I would appreciate it very much if someone
> could help me out. We use Oracle 9.
>
> I have 1 table named contents.
> This table has the following fields:
> NR number(9) NOT NULL - the unique identifier
> PARENT number(9) NOT NULL - the NR of the item which is the parent
> of this item (child)
> FOLDER number(1) NOT NULL - Boolean defining if the item is capable
> of having children
> TITLE varchar2(256) NOT NULL - the title of the item
> SORTINDEX number(3) - the value that defines the order of items
> CONTENTTYPE number(5) NOT NULL - a value for a related table
>
> The table looks like this (more or less) NR 56018 is the root which is
> not important for this example:
> NR PARENT FOLDER TITLE SORTINDEX CONTENTTYPE
> 55580 56018 1 Home 0 644
> 55581 55580 1 First 0 644
> 55582 55580 1 Second 1 644
> 55583 55580 1 Third 2 644
> 55584 55580 0 Fourth 3 644
> 55585 55581 0 First - 1 0 644
> 55586 55581 0 First - 3 1 644
> 55587 55581 1 First - 2 2 644
> 55588 55582 0 Second - 1 0 644
> 55589 55583 1 Third - 1 0 644
> 55590 55583 0 Third - 2 1 644
> 55591 55587 0 First - 2 - 1 0 644
> 55592 55589 0 Third - 1 - 1 0 644
>
> No problems with the straight forward queries... but in this
> particular case I would like to make a query that returns a list of
> items ordered by the value in sortindex and grouped by parentfolder
> within the range of the contenttype 644.
>
> Which output should look something like this (hyphens show with
> folders, square-brackets show with items):
> Home (55580)
> First (55581)
> First - 1 [55585]
> First - 2 (55587)
> First - 2 - 1 [55591]
> First - 3 [55586]
> Second (55582)
> Second - 1 [55588]
> Third (55583)
> Third - 1 (55589)
> Third - 1 - 1 [55592]
> Third - 2 [55590]
> Fourth (55584)
>
> Who could help me with the query on this? I can't order on either of
> the fields to render this list, the only solution I came up with was
> trying to build a row number with 'sortorder of parent's
> parent'+'sortorder of child's parent'+'sortorder of child'... but I
> don't have a clue on how to build that. Or is there a much easier way?
> (Please say yes and show me :)
>
> The actual list must be flexible in depth so the example above only
> shows 3 layers but it could be as much as 9 layers...
> I really really hope you can help me solve this riddle,
> Thanks in advance...
>
> M_at_r(o
This is, basically, a standard connect by query:
SQL>
SQL> create table contents(
2 NR number(9) NOT NULL, 3 PARENT number(9) NOT NULL, 4 FOLDER number(1) NOT NULL, 5 TITLE varchar2(256) NOT NULL, 6 SORTINDEX number(3), 7 CONTENTTYPE number(5) NOT NULL,8 constraint contents_pk
9 primary key(nr)
10 );
Table created.
SQL> SQL> SQL> insert all
2 into contents
3 values(55580,56018,1,'Home',0,644)
4 into contents
5 values(55581,55580,1,'First',0,644) 6 into contents
7 values(55582,55580,1,'Second',1,644) 8 into contents
9 values(55583,55580,1,'Third',2,644) 10 into contents
11 values(55584,55580,0,'Fourth',3,644) 12 into contents
13 values(55585,55581,0,'First - 1',0,644) 14 into contents
15 values(55586,55581,0,'First - 3',1,644) 16 into contents
17 values(55587,55581,1,'First - 2',2,644) 18 into contents
19 values(55588,55582,0,'Second - 1',0,644) 20 into contents
21 values(55589,55583,1,'Third - 1',0,644) 22 into contents
23 values(55590,55583,0,'Third - 2',1,644) 24 into contents
25 values(55591,55587,0,'First - 2 - 1',0,644) 26 into contents
27 values(55592,55589,0,'Third - 1 - 1',0,644) 28 select * from dual;
13 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> with ord_recs as(
2 select title, nr, parent 3 from contents 4 order by title
5 )
6 select lpad(' ', level -1)||title||'('||nr||')' 7 from ord_recs
8 connect by parent = prior nr
9 start with nr = 55580
10 /
LPAD('',LEVEL-1)||TITLE||'('||NR||')'
Home(55580)
First(55581)
First - 1(55585)
First - 2(55587)
First - 2 - 1(55591)
First - 3(55586)
Fourth(55584)
Second(55582)
Second - 1(55588)
Third(55583)
Third - 1(55589)
Third - 1 - 1(55592)
Third - 2(55590)
13 rows selected.
SQL>
SQL> delete from contents;
13 rows deleted.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> insert all
2 into contents
3 values(55580,56018,1,'Home',0,644)
4 into contents
5 values(55581,55580,1,'First',0,644)
6 into contents
7 values(55582,55580,1,'Second',1,644)
8 into contents
9 values(55583,55580,1,'Third',2,644)
10 into contents
11 values(55584,55580,0,'Fourth',3,644)
12 into contents
13 values(55585,55581,0,'First - 1',0,644)
14 into contents
15 values(55586,55581,0,'First - 3',1,644)
16 into contents
17 values(55587,55581,1,'First - 2',2,644)
18 into contents
19 values(55588,55582,0,'Second - 1',0,644)
20 into contents
21 values(55589,55583,1,'Third - 1',0,644)
22 into contents
23 values(55590,55583,0,'Third - 2',1,644)
24 into contents
25 values(55591,55587,0,'First - 2 - 1',0,644)
26 into contents
27 values(55592,55589,0,'Third - 1 - 1',0,644)
28 into contents
29 values(55593,55590,1,'Fifth',0,644)
30 into contents
31 values(55594,55591,1,'Sixth',0,644)
32 select * from dual;
15 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> with ord_recs as(
2 select title, nr, parent 3 from contents 4 order by title
5 )
6 select lpad(' ', level -1)||title||'('||nr||')' 7 from ord_recs
8 connect by parent = prior nr
9 start with nr = 55580
10 /
LPAD('',LEVEL-1)||TITLE||'('||NR||')'
Home(55580)
First(55581)
First - 1(55585)
First - 2(55587)
First - 2 - 1(55591)
Sixth(55594)
First - 3(55586)
Fourth(55584)
Second(55582)
Second - 1(55588)
Third(55583)
Third - 1(55589)
Third - 1 - 1(55592)
Third - 2(55590)
Fifth(55593)
15 rows selected.
SQL> I'll leave it to you to get your brackets and parenthesis where you want them.
David Fitzjarrell Received on Thu Jan 10 2008 - 08:23:28 CST