Thankyou very much, that has simplified things no end ... speeded the query
up as well ;-D
--
James Alexander Starritt (james_at_jamesstarritt.com)
"rr" <raineyr_at_look.ca> wrote in message news:3A708802.FE029310_at_look.ca...
> from Oracle docs:
>
> To define a hierarchical relationship in a query, you must use the START
WITH
> and CONNECT BY clauses.
>
> LEVEL
>
> For each row returned by a hierarchical query, the LEVEL pseudocolumn
> returns 1 for a root node, 2 for a child of a root, and so on. A root node
is
> the highest node within an inverted tree. A child node is any nonroot
node. A
> parent node is any node that has children. A leaf node is any node without
> children. Figure 2-2 shows the nodes of an inverted tree with their LEVEL
> values.
>
>
> CONNECT BY
>
> If a table contains hierarchical data, you can select rows in a
hierarchical
> order using the hierarchical query clause:
>
> START WITH specifies the root row(s) of the hierarchy.
>
> CONNECT BY specifies the relationship between parent rows and child
rows of
> the hierarchy. Some part of condition must use the PRIOR operator to refer
to
> the parent row. See the PRIOR operator.
>
> WHERE restricts the rows returned by the query without affecting
other rows
> of the hierarchy.
>
> Oracle uses the information from the hierarchical query clause clause to
form
> the hierarchy using the following
> steps:
>
> 1.Oracle selects the root row(s) of the hierarchy--those rows that
satisfy
> the START WITH condition.
>
> 2.Oracle selects the child rows of each root row. Each child row must
satisfy
> the condition of the CONNECT BY condition with respect to one of the root
rows.
>
> 3.Oracle selects successive generations of child rows. Oracle first
selects
> the children of the rows returned in step 2, and then the children of
those
> children, and so on. Oracle always selects children by evaluating the
CONNECT BY
> condition with respect to a current parent row.
>
> 4.If the query contains a WHERE clause, Oracle eliminates all rows from
the
> hierarchy that do not satisfy the condition of the WHERE clause. Oracle
> evaluates this condition for each row individually, rather than removing
all the
> children of a row that does not satisfy the condition.
>
> 5.Oracle returns the rows in the order shown in Figure 5-1. In the
diagram
> children appear below their parents.
>
> Figure 5-1 Hierarchical Queries
>
> To find the children of a parent row, Oracle evaluates the PRIOR
expression of
> the CONNECT BY condition for the parent row and the other expression for
each
> row in the table. Rows for which the condition is true are the children of
the
> parent. The CONNECT BY condition can contain other conditions to further
filter
> the rows selected by the query. The CONNECT BY condition cannot contain a
> subquery.
>
> If the CONNECT BY condition results in a loop in the hierarchy, Oracle
returns
> an error. A loop occurs if one row is both the parent (or grandparent or
direct
> ancestor) and a child (or a grandchild or a direct descendent) of another
row.
>
> If I can recall, I think that would mean :
> Select Level, Genre_ID, Genre
> from terms
> start with genre_id
> connect by Broader_ID;
>
> rachel
>
> James Alexander Starritt wrote:
>
> > Ok, I have come across a problem that I am determined to beat using SQL
and
> > have not managed to. My theory is right but my implementation might be
> > sloppy, so I am looking for a more refined statement and little help on
a
> > wee hitch I am having with the problem.
> >
> > I have a table which for the sake of this example will be called Terms
> >
> > Genre_ID (num key), Genre, Broader_ID
> >
> > This table was desinged (not by me) to hold a heirachy of terms. Each
term
> > is linked to its successor where Broader_ID = Genre_ID. This has been
just
> > fine for the way we have used it in the past but a user has asked for us
to
> > produce a heirachy report thats shows a Genre and its Sub Genre's ....
The
> > maximum depth is 12. I being reletivly new to SQL, dived into Microsoft
> > Access 2000, and using there qyuery tools and an ODBC link created a
query
> > that included Genre and twelve aliases of Genre.
> >
> > I guess I was trying to produce a recursive search of sorts and it
worked
> > perfectly if slowly. 28,000 terms.
> >
> > I then started to create the query in Oracle SQL Plus. This is what I
have
> > come up with:
> >
> > select a.genre, b.genre, c.genre, d.genre, e.genre, f.genre, g.genre,
> > h.genre, i.genre, j.genre, k.genre, l.genre
> > from
> > terms a, terms b, terms c, terms d, terms e, terms f, terms g,
> > terms h, terms i, terms j, terms k, terms l
> > where
> > b.broader_id = a.genre_id
> > AND c.broader_id = b.genre_id
> > AND d.broader_id = c.genre_id
> > AND e.broader_id = d.genre_id
> > AND f.broader_id = e.genre_id
> > AND g.broader_id = f.genre_id
> > AND h.broader_id = g.genre_id
> > AND i.broader_id = h.genre_id
> > AND j.broader_id = i.genre_id
> > AND k.broader_id = j.genre_id
> > AND l.broader_id = k.genre_id
> >
> > Using this query I can display the full heirachy top down. The problem
comes
> > when I want to display a branch. If I add critea ie
> >
> > ....
> > AND k.broader_id = j.genre_id
> > AND l.broader_id = k.genre_id
> > AND a.genre = 'Tools';
> >
> > I get no rows returned! ... It works in Access but not in Oracle and I
know
> > its me not creating the SQL statements in either the wrong order
something
> > similar. Here is the Access statement for those with a familiarity with
it
> > ...
> >
> > SELECT TERMS.GENRE, TERMS_1.GENRE, TERMS_2.GENRE, TERMS_3.GENRE,
> > TERMS_4.GENRE, TERMS_5.GENRE, TERMS_8.GENRE, TERMS_7.GENRE,
TERMS_6.GENRE,
> > TERMS_9.GENRE
> > FROM ((((((((TERMS LEFT JOIN TERMS AS TERMS_1 ON TERMS.GENRE_ID =
> > TERMS_1.BROADER_ID) LEFT JOIN TERMS AS TERMS_2 ON TERMS_1.GENRE_ID =
> > TERMS_2.BROADER_ID) LEFT JOIN TERMS AS TERMS_3 ON TERMS_2.GENRE_ID =
> > TERMS_3.BROADER_ID) LEFT JOIN TERMS AS TERMS_4 ON TERMS_3.GENRE_ID =
> > TERMS_4.BROADER_ID) LEFT JOIN TERMS AS TERMS_5 ON TERMS_4.GENRE_ID =
> > TERMS_5.BROADER_ID) LEFT JOIN TERMS AS TERMS_8 ON TERMS_5.GENRE_ID =
> > TERMS_8.BROADER_ID) LEFT JOIN TERMS AS TERMS_7 ON TERMS_8.GENRE_ID =
> > TERMS_7.BROADER_ID) LEFT JOIN TERMS AS TERMS_6 ON TERMS_7.GENRE_ID =
> > TERMS_6.BROADER_ID) LEFT JOIN TERMS AS TERMS_9 ON TERMS_6.GENRE_ID =
> > TERMS_9.BROADER_ID
> > WHERE (((TERMS.GENRE)=[Enter Term]));
> >
> > Any help with this problem would be appreciated, especially any advice
on
> > making this a little more efficient. I can't change the data structure
so I
> > am stuck with this for now .....
> >
> > --
> >
> > James Alexander Starritt (james_at_jamesstarritt.com)
>
Received on Thu Jan 25 2001 - 13:10:56 CST
