Published: Wednesday, October 06, 1999
Advanced Data Shaping Techniques
| For More Advanced Information on Data Shaping |
|---|
|
For more advanced information on data shaping be sure to check out the following articles:
|
In a previous article, I (Scott Mitchell) showed how to use Data Shaping to return a recordset as an element of another recordset. If you are unfamiliar with Data Shaping, I strongly suggest you read the precursor article first.
Data Shaping, as we discussed earlier, can be used to easily group data which resembles a parent-child relationship. In the previous article, I used the example of professors and classes. Each professor can teach zero to many classes, while each class has exactly one professor. Recall that Data Shaping returns an recordset representing the parent item; in each row, a column represents the children of that particular parent, which is a recordset object itself! So, using Data Shaping, you will have recordsets inside of recordsets. The previous article on Data Shaping discusses this technology in detail.
While single parent-child data shapes may be useful and interesting, the true power of Data Shaping is revealed with multiple children and grandchildren data shapes! Imagine that a particular "parent" has two "children" that you would like to represent as a data shape. For example, in our professor/classes example, imagine that we also have a table that contains a list of all of the research projects that are being worked on. Each research project has exactly one professor assigned to it, and professors can be assigned to multiple research projects. Here are the table definitions for both Professor,
Class, and Project tables:
Professor |
|---|
| ProfessorID | Autonumber |
| FirstName | Text |
| LastName | Text |
| PhoneNumber | Text |
Class |
|---|
| ClassID | Autonumber |
| ClassName | Text |
| RefNumber | Text |
| ProfessorID | Number |
Project |
|---|
| ProjectID | Autonumber |
| Title | Text |
| ProfessorID | Number |
The ProfessorID in both the Project and Class tables are foreign keys to the ProfessorID in the Professor table. A quick examination of the table designs show that each professor can have zero to many classes and zero to many research projects. Looks like we've got a parent (a professor) with two children (classes taught and research projects). It would be nice to hierarchically display the results: each professor would be listed, followed by the classes he or she taught, and then the research projects he or she worked on.
Such a nice, hierarchical display can be achieved with using Data Shaping. Recall that the syntax for data shaping is:
SHAPE {parent SQL statement} [[AS] alias]
APPEND({child SQL statement} [[AS] alias]
RELATE parent column TO child column) [[AS] alias]
|
To add multiple children, simply do:
SHAPE {parent SQL statement} [[AS] alias]
APPEND({child SQL statement} [[AS] alias]
RELATE parent column TO child column) [[AS] alias]{,
({child SQL statement} [[AS] alias]
RELATE parent column TO child column) [[AS] alias]}
|
We can add many children to one data shape. Each child is added by simply adding a comma and another child SQL statement and RELATE statement. So, to show a our professors, their classes, and their projects, the following data shape command could be used:
<![CDATA[
SHAPE {SELECT ProfessorID, FirstName, LastName, PhoneNumber FROM Professor}
APPEND({SELECT ClassName, RefNumber, ProfessorID FROM Class} AS Class
RELATE ProfessorID TO ProfessorID),
({SELECT Title, ProfessorID FROM Project} AS Project
RELATE ProfessorID TO ProfessorID)
]]>
|
Now that we've shown the proper SHAPE command, let's examine how to write code to utilize it. We'll examine the needed code in Part 2!
Read Part 2!
