Re: Looking for a different slant on this SQL Problem

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 - 12:08:22 CST