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
. 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
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