Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Jul 16, 2010

Dynamic Data Driven Variable Hierarchical Structures in SQL

By Michael M. David

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.

Review of linking below

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.

Structure generation by looking back

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.

Structure generation by looking forward

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.

Structure generation using multiple choices

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

Multipath structure generation

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.

Nested structure generation


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.

» See All Articles by Columnist Michael M. David

SQL etc Archives

Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM