VDMGEN is a simple, flexible and expandable framework for building and maintaining SQL/DDL/DML or PHP code libraries. It is perfectly suited for automating the maintenance of MQTs and other derived data in the database, or for application data access and other layered components. Because of its simplicity and openness it can be expanded to other uses as well.
Whether it is accounts with aging balances, or inventory aging, the patterns are the same. This example illustrates how by using cumulative sums of sales/receipts, we can compute a baseline result set from which aging queries can be answered.
Assume a series of account transactions. We want for every transaction of each account to display the latest activation for the transaction. We use the LAST_VALUE function. The DDL file defines the tables and loads some data used by the SQL script.
These scripts compare the outcome of OLAP LAG, LAST_VALUE and FIRST_VALUE functions depending on the parameters used and the existence of nulls. Modify the common table expression values to test your own variants.
The examples in the script below show the simple ancestor-descendent traversal queries such as finding the ancestors or descendents of a node. It also shows the rollup of transactions by the account hierarchy. The last query does the same thing but instead of explicitly doing the between-join in the query, it uses a view that hides this complexity.
Notice that the posting nodes of the account hierarchy have a PostSign attribute that determines whether to negate the transaction amount in order to make expenses always negative and revenue always positive.
These example scripts describe the technique of topological ordering of hierarchies. They start by setting up a working set of tables and continue with a working example of the enumeration algorithm followed by use cases of ancestor-descendent queries and computational hierarchies.
This material is referenced by presentation E12 of the IDUG EMEA 2010.
Take a look at the The Relational Taboo page for the original article circa 1992. Also refer to Joe Celko's books for the basics of the technique.
The following two scripts illustrate the use of topologically ordered hierarchies to rollup account details. At each level, there is an indicator that determines the sign of the rollup to the next level. A view is defined that simple links any ancestor-descendent pair in the account hiearchy and determines if the descendent needs to be negated or not.
The first file is the DDL and it also loads some data.
The second file progressively demonstrates how the method works.
The enumeration method consists of a depth first left-to-right traversal of the hierarchy, assigning the left number when first visiting a node when last visiting it after all descendent nodes have been enumerated. To achieve this, imagine a thread created starting from the left of the top node, progressing through the left side of the first descendent, wrapping around the first leaf through its right side, up the right of the parent and so on...
Now imagine this thread streatched out. Each node appears twice. Once attached on the left, and once on the right. The objective is to order the nodes of the hierarchy in exactly the order visited by the thread, then enumerate the list, and pivot the left and right numbers against each node.