Reshaping Structures Without Using Relationships
The examples you've seen so far have showed restructuring using data relationships to perform the required joins. However, if the data relationships are not available, there is still another way to perform the transformation—by utilizing the semantics in the data structure to move the desired node(s) to a new location on the structure. This feature allows any-to-any structure reshaping; however it's a different operation than the restructuring operations you've seen, and will usually produce different results.
Restructuring uses data relationships existing in the structure to introduce different semantics, while
reshaping changes the hierarchical structure's shape while retaining the same basic data semantics. Restructuring gives you the structure data semantics you want, and reshaping gives you the data structure you want while maintaining the semantics of the data.
You reshape a single structure using two or more copies of that structure, and left joining on the previous node compared to itself, repeating the operation for each node or fragment to be attached to the resulting structure. The example in
Figure 7 reshapes a nonlinear multipath structure to a linear structure without using any matching relationships in the structure to be reshaped using this SQL:
 | |
| Figure 7. Reshaping Structures: Reshaping does not use data relationships and does not change the basic semantics of the data in the original structure. |
SELECT SV1.EaddrID, SV2.EmpID, SV2.DpndID
FROM EmpView SV1 LEFT JOIN EmpView SV2
ON SV1.EaddrID = SV2.EaddrID
In
Figure 7, note that the first root node of the new reshaped structure is
Eaddr, so this node gets moved to the result structure to start the process. Next, the first comparison compares this
EaddrID to itself in both copies, to synchronize and position the copies. Then the second node,
Emp, gets moved over from the synchronized copy using a
LEFT JOIN, which produces
Eaddr over
Emp. This process relies heavily on linking below the root of the lower structure, which works out nicely since most of the time the node you are after is not the root.
Finally, the same processes can be repeated to bring the
Dpnd node over by synchronizing on the
Emp node. However, there's a shortcut that can reduce the number of steps needed to move all the desired nodes over. Without the shortcut, building the output structure will always require a fixed number of steps—the number of nodes in the output structure minus one. Notice in
Figure 7 that the
Dpnd node is
already positioned below the
Emp node, so it can simply be moved over along with the
Emp node, saving a synchronization join step. You can see this reflected in the
SELECT statement in the SQL, where the lower-level
SV2 prefix is used for both
EmpID and
DpndID to select them together.
One again, solid boxes represent selected items. The unselected dashed boxes are not moved to the output structure. You can then see how the reshaping was accomplished by looking at the selected fields from the combined structure. The
LEFT JOIN operations both perform the data modeling and also serve to copy the needed data into place.
For additional information on Reshaping, see the article, "
ANSI SQL Semantically Controlled Any-to-Any Data Structure Reshaping."
Previous Page
