 | |
Oracle 9 Query question...
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Oracle 9 Query question... -
01-10-2008
, 07:37 AM
#2
| |||
| |||
|
|
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@r(o |
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
#3
| |||
| |||
|
|
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@r(o |
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
#4
| |||
| |||
|
|
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@r(o |
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
#5
| |||
| |||
|
|
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@r(o |
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
#6
| |||
| |||
|
|
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@r(o |
no indication you've put even one bit of effort into solving the problem
so you shouldn't have anyone here do your homework for you.
But you've given the answer to the problem in your question.
Look up LPAD and RPAD functions and you should be able to solve this
yourself or get close enough for someone to help you with the final
little bit.
Please make an effort to solve it and then post your best solution,
and a version number, if you still need help.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#7
| |||
| |||
|
|
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@r(o |
no indication you've put even one bit of effort into solving the problem
so you shouldn't have anyone here do your homework for you.
But you've given the answer to the problem in your question.
Look up LPAD and RPAD functions and you should be able to solve this
yourself or get close enough for someone to help you with the final
little bit.
Please make an effort to solve it and then post your best solution,
and a version number, if you still need help.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#8
| |||
| |||
|
|
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@r(o |
no indication you've put even one bit of effort into solving the problem
so you shouldn't have anyone here do your homework for you.
But you've given the answer to the problem in your question.
Look up LPAD and RPAD functions and you should be able to solve this
yourself or get close enough for someone to help you with the final
little bit.
Please make an effort to solve it and then post your best solution,
and a version number, if you still need help.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#9
| |||
| |||
|
|
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@r(o |
no indication you've put even one bit of effort into solving the problem
so you shouldn't have anyone here do your homework for you.
But you've given the answer to the problem in your question.
Look up LPAD and RPAD functions and you should be able to solve this
yourself or get close enough for someone to help you with the final
little bit.
Please make an effort to solve it and then post your best solution,
and a version number, if you still need help.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#10
| |||
| |||
|
|
M@r(o 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) * * * * * * * NOTNULL * * * *- the NR of the item which is the parent of this item (child) FOLDER * * * * * * number(1) * * * * * * * NOTNULL * * * *- Boolean defining if the item is capable of having children TITLE * * * * * * *varchar2(256) * NOT NULL * * * *- the title of the item SORTINDEX *number(3) * * * * * * * * * * * - thevalue 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@r(o This is obviously school work and you've included no version number and no indication you've put even one bit of effort into solving the problem so you shouldn't have anyone here do your homework for you. But you've given the answer to the problem in your question. Look up LPAD and RPAD functions and you should be able to solve this yourself or get close enough for someone to help you with the final little bit. Please make an effort to solve it and then post your best solution, and a version number, if you still need help. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Groupwww.psoug.org- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - |
I am sorry to say you're wrong about that. I am a 41 year old
webmaster who has some knowledge and experience of Oarcle Queries.
I've been struggling with the problem I stated for the last two
weeks... clearly I'm no crack at Oracle. So, I made the effort more
than twice... And I did state the version number; 'We use Oracle 9.'
but I guess that's not specific enough?
I am sorry that it looked that way though, but the problem I describes
is a tuned-down version of the real problem I have here.
So be carefull with your assumptions... ;-)
And David, thank you for the effort.
Kind regards,
Marco Hoefman
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.