|
Empress Technical News – September
2009
Empress Hierarchical Query –
Retrieve Hierarchical Data Naturally
or Tree Walking Made Easy
Introduction
Empress Ultra Embedded 10.20 offers many useful features for
application developers. One of those features is the Empress Hierarchical
Query.
Empress Hierarchical Query
A standard relational
database does not store data in a hierarchical way. How can you get data from a
relational database in a hierarchical manner?
The new Empress Hierarchical Query feature is the answer.
Empress has extended its
relational data management system to satisfy data retrievals that better
reflect real world requirements. In many cases, these real world requirements deal
with data that is hierarchical in nature. Performing those retrievals in the
most efficient and natural manner has been an objective of many database application
developers.
Empress Hierarchical
Query is an extension of a self-join and works on a
single table that contains data in a virtual hierarchical data structure.
An example of a
hierarchical structure is a company organizational chart. We will use the
sample personnel table data from the Empress User’s Manual and add title
and manager_id attributes in order to make a hierarchical structure.
The personnel table
is given in the Figure 1. Its
hierarchical representation is shown in the Figure 2.
Figure 1. personnel table data
Figure 2. A hierarchical
representation of the personnel table (organizational chart)
If a table containshierarchical data, you can retrieve table records in a hierarchical order using
the following clauses:
START WITH – You can specify the starting root record of the
hierarchy using this clause.
CONNECT BY – You can
specify the relationship between parent records and child records of the
hierarchy using this clause.
WHERE – You can restrict the records returned by the query
without affecting other records of the hierarchy using this clause.
A simplified SELECT syntax
that includes support for the hierarchical queries is as follows:
SELECT …
FROM table_expression
…
[ WHERE_CLAUSE ]
[ START WITH start_with_condition
]
CONNECT BY connect_condition
[ SORT_CLAUSE ];
Empress uses the
information in these clauses to form a hierarchy by doing the following steps:
1. Empress selects the root
record(s) of the hierarchy. These are the records that satisfy the condition start_with_condition.
2. Empress selects the
child records of each root record. Each child record must satisfy the condition
connect_condition with respect to one of the root records.
3. Empress selects
successive generation of the child records. Empress first selects the children
of the records returned by step 2, and then the children of those children, and
so on. Empress always selects children by evaluating the connect_condition with
respect to a current parent record.
4. If the query contains a
WHERE_CLAUSE, Empress removes all records from the hierarchy that do not
satisfy the condition of the WHERE_CLAUSE. Empress evaluates the condition for
each record individually, rather than removing all the children of a record
that doesn't satisfy the condition.
start_with_condition identifies the record(s) to be used as the root(s) of
a hierarchical query. The clause specifies a condition that the roots must
satisfy. If you omit this clause, Empress will use all records in the table as
root records.
connect_condition specifies the relationship between parent and child records
in a hierarchical query. This clause contains a condition that defines this relationship.
connect_condition
includes the PRIOR operator that must have one of these
forms:
PRIOR expr
comparison_operator expr
expr comparison_operator PRIOR expr
The connect_condition can
contain other conditions to further filter the records by the query.
In the following example we
are going to create table personnel, populate it with data and execute
several retrieval commands utilizing Empress Hierarchical Query.
SQL> CREATE personnel (id
SMALLINT, name CHAR (10),
phone CHAR (10), title CHAR (15),
manager_id SMALLINT);
SQL> INSERT INTO personnel
(id, name, phone, title, manager_id)
VALUES (
10,"Kilroy","426-9681","SW
DEVELOPER",17,
5,"Mosca","544-2243","SALES
REP",8,
17,"Wladislaw","723-6073","R&D
MANAGER",4,
3,"Jones","667-2951","TESTER",17,
8,"Peterson","978-6060","SALES
MANAGER",4,
4,"Scarlatti","961-7363","CEO",NULL,
9,"Jordan","964-3335","CFO",4
);
The following commandselects all the records from the table personnel.
SQL> SELECT * FROM PERSONNEL;
id
name phone title manager_id
10
Kilroy 426-9681 SW DEVELOPER 17
5
Mosca 544-2243 SALES REP 8
17
Wladislaw 723-6073 R&D MANAGER 4
3
Jones 667-2951 TESTER 17
8
Peterson 978-6060 SALES MANAGER 4
4
Scarlatti 961-7363 CEO
9 Jordan 964-3335
CFO 4
The following command
selects all the records from the table personnel in the hierarchical
manner starting with the record that has title equal to “CEO”.
SQL>
SELECT *
FROM personnel
START WITH title="CEO"
CONNECT BY PRIOR id=manager_id;
id
name phone title manager_id
4
Scarlatti 961-7363 CEO
17
Wladislaw 723-6073 R&D MANAGER 4
10
Kilroy 426-9681 SW DEVELOPER 17
3 Jones
667-2951 TESTER 17
8
Peterson 978-6060 SALES MANAGER 4
5
Mosca 544-2243 SALES REP 8
9 Jordan 964-3335 CFO 4
The following command
selects all the records from the table personnel in the hierarchical
manner starting with the record that has title equal to “SALES MANAGER”.
SQL>
SELECT *
FROM personnel
START WITH title="SALES MANAGER"
CONNECT BY PRIOR id=manager_id;
id
name phone
title manager_id
8
Peterson 978-6060 SALES MANAGER 4
5
Mosca 544-2243 SALES REP 8
The following command
selects all the records from the table personnel in the hierarchical
manner using only two levels of hierarchy and starting with the record that has
title equal to “CEO”. In order to specify only two levels of hierarchy a pseudo
column LEVEL is used (i.e. LEVEL < 3)
SQL>
select *
from personnel
START WITH title="CEO"
CONNECT BY PRIOR id=manager_id and LEVEL<3;
id
name phone title manager_id
4
Scarlatti 961-7363 CEO
17
Wladislaw 723-6073 R&D MANAGER 4
8
Peterson 978-6060 SALES MANAGER 4
9 Jordan 964-3335 CFO 4
Instead of Summary
Using Empress Hierarchical Queries, you can easily retrieve
records by their natural hierarchical relationship from a RDBMS table. Tree
walking enables you to walk a virtual hierarchical tree, for relationships in
the same table, and select data by their natural hierarchical relationship.
Examples of hierarchical data include:
- employees
in an organizational chart
- data
in a bill of materials scenario in which a product has one or more components and
those components have subcomponents
and so on
- a
family tree
- complex
project data consisting of many task and subtasks representing multiple levels
of hierarchy
- manufacturing
operation charts with many levels
Empress Hierarchical Queries are
included in the latest Empress Ultra Embedded V10.20.
Empress Hierarchical Queries allow you
to traverse virtual trees in a relational database using any Empress SQL
interface, ODBC, JDBC and the Empress kernel level MR interface. Just give
Empress a hierarchical query clause with the record representing the starting
node of the tree and the relationship between parent and child nodes and you
get the data you want.
Empress Software Inc.
www.empress.com
|
