Re: Oracle 9 Query question...

On 10 jan, 15:26, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_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 -

Hello Daniel,

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 Received on Fri Jan 11 2008 - 03:59:01 CST