Dynamic Data Driven Variable Hierarchical Structures in SQL
July 16, 2010
SQL can support the creation of variable hierarchical structures in a number of differently controlled operations that can be combined. Michael M. David describes a powerful and flexible automatic method of dynamic structure generation.
Review of Already Covered Dynamic Structure Operations
SQL can support the creation of variable hierarchical structures in a number of differently controlled operations that can be combined. Three have been shown in the previous articles in this series on SQL hierarchical processing. These have been dynamic: hierarchical structure data modeling; hierarchical structure joining; and SQL's variable SELECT list to specify the output. This article describes data driven variable structure generation and in a future article variable structure creation will be described using structure transformations. All of these different structure operations all generate structures in a different way.
Review of Linking Below Root
The capability of linking below the root was first described in the previous article. This capability is also used in supporting data driven variable structures. So this section is repeated from the previous article and it also uses the same data and structures definitions which are also shown here in figure 1.
Using the SELECT statement in Figure 1 below to join the defined ABC and XYZ view structures hierarchically, the lower level structure's root node X has semantic control over node Z even though the Z node is used as the lower structure's link point. This linking operation should not hierarchically elevate the Z node above the X node. The data link points between the structures being combined are used to match up data relationships between the structures being combined. This is actually a data filtering operation applied at the lower structure's Z node and will filter the entire lower structure hierarchically from the Z node outward in all path directions. This is possible for non relational data because all data has been placed in relational rowsets.
The lower level structure has its hierarchical structure established before the linking process and should be treated as a single fixed entity regardless of where it is linked to even if it is a logical structure. SQL does this automatically when it materializes the view. Since node Z is already affected and controlled by other nodes above it, the hierarchical data modeling link point is the root node X of the lower structure, as is shown in Figure 1, while the data relationship data filtering occurs between nodes C and Z. This consistently preserves the data semantics and structures.
Variable Structure Creation by Looking Back
While the ON clause is meant to make the relationship between two data structures, it can also specify data filtering that is specified in the same local manner to reference data at the join point or further up the active pathway. This is shown in Figure 2 below where AND A.a=2 has been added to the SELECT statement as shown. This has the effect of causing the XYZ view join to take place or not to take place depending on if A.a=2 is true or not. This occurs whenever this join operation is performed so it can occur on and off as needed. This causes a variable structure generation controlled by data value. The two possible structure generations are shown in Figure 2.
Variable Structure Creation by Looking Forward
In the previous example in Figure 2, the variable structure was controlled by a data value further up the pathway, but as seen with linking below the structure root in Figure 1, data references can also be made further down the structure. This is shown in Figure 3 where a data value further down the pathway controls whether a join takes place or not in order to control variable structures. The XYZ view must be accessed in order to test if it is needed because it contains the data item needing testing. This is a look-a-head operation, which is performed automatically in SQL because of its view materialization processing.
Variable Structure Creation Using Multiple Choice Selection
So far in the examples shown, the choice has been controlling whether a substructure is dynamically created or skipped. It is possible to have a choice of selecting one from any number of different structures. This is performed by including multiple LEFT Joins with different views as shown below in Figure 4. Testing different data items allows more than one view selection generated at a time.
Multipath Variable Structure Creation
Variable structure creation can also be applied independently to any number of pathways in a structure. This is performed by using multiple LEFT Joins as in the previous example in Figure 4 except the LEFT Joins target different pathways in the structure. This is shown in Figure 5 where look back and look forward methods are both used. Not shown is that both variable structures can be generated since they are testing a different data sources (A.a and O.o).
Nested Variable Structure Creation
Nested structure generation can be applied top down. This is shown in Figure 6 where view XYZ is applied variably following view ABC, and view MNO is applied to view XYZ variably. The XYZ view itself could variably invoke the MNO view. When this is known in advance, this is a better more powerful and flexible way to perform variable nested structure generation because it uses reuse automatically and abstracts the complexity of nesting.
The data driven variable structure generation shown in this article is a very powerful and flexible automatic method of dynamic structure generation. The different data driven techniques shown can be combined as needed. The SQL hierarchical examples in this article can be tested using my company's online interactive ANSI SQL Transparent XML Hierarchical Processor prototype.