Re: Oracle 9 Query question...

M_at_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) 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 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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org