Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Hierarchical queries
- Thread starter williamn
- Start date
- Thread starter
- #4
First, Happy New Year.
I worked with Oracle a time ago. I know that Oracle has some special query clauses to make hierarchical queries. I need to know if DB2 has something similar.
Below it is a passage of the Oracle documentation:
Hierarchical Queries
If a table contains hierarchical data, you can select rows in a hierarchical order using the hierarchical query clause:
START WITH <condition> CONNECT BY <condition>
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.
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.
Thanks,
William
I worked with Oracle a time ago. I know that Oracle has some special query clauses to make hierarchical queries. I need to know if DB2 has something similar.
Below it is a passage of the Oracle documentation:
Hierarchical Queries
If a table contains hierarchical data, you can select rows in a hierarchical order using the hierarchical query clause:
START WITH <condition> CONNECT BY <condition>
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.
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.
Thanks,
William
Hi William,
download one of the Cookbooks that exists in this site and take a look in the Chapter "Recursive SQL"
Regards,
Alphonsus.
download one of the Cookbooks that exists in this site and take a look in the Chapter "Recursive SQL"
Regards,
Alphonsus.
- Status
Similar threads
