Performing Hierarchical Restructuring Using ANSI SQL : Page 4

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