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