Data Shaping, Part 2

---

We've examined how to retrieve the Class and Professor tables using JOINs. While there is nothing wrong with using JOINs, some folks find the syntax tricky. Also, the data received needs some formatting. In Part 1, when we listed the professors and the classes they taught, a professor's name was listed as many times as the number of classes he or she taught! It would be nice to have the output formatted like:

Dr. Mitchell
    Formal Languages & Automata Theory
    Study of Film
    Software Engineering

While this can be done using a little ASP logic, it is a bit nasty.

Data shaping is a new feature of ADO 2.0. It allows us to grab data from a database, and puts the related data in a recordset. So, in our example, we could bring back a simple recordset of professors, which could contain their FirstName, LastName, PhoneNumber, and classes taught. The classes taught field would be a recordset itself, containing all of the class information! This may seem a bit mind blowing, since we are retrieving recordets containing recordsets! This is why data shaping is so cool, though!

We have to use a special syntax for data shaping.

SHAPE {parent SQL statement} [[AS] alias] APPEND({child SQL statement} [[AS] alias] RELATE parent column TO child column) [[AS] alias]

The parent SQL statement and the child SQL statement are both just simple SQL statements. If we wanted to obtain a list of professors and classes they taught, the SQL statement to retrieve the list of professors would be the parent SQL statement, while the SQL statement to retrieve the list of classes would be the child SQL statement. The two statements would be related on ProfessorID. Therefore, the syntax for our data shape would look like:

SHAPE {SELECT ProfessorID, FirstName, LastName, PhoneNumber FROM Professor} APPEND({SELECT ClassName, RefNumber, ProfessorID FROM Class} AS Class RELATE ProfessorID TO ProfessorID)

What if we wanted to obtain a listing of the classes and the professors that taught them? Well, we'd simply reverse the parent and child SQL statements. The syntax would change to:

SHAPE {SELECT ClassName, RefNumber, ProfessorID FROM Class} APPEND({SELECT ProfessorID, FirstName, LastName, PhoneNumber FROM Professor} AS Prof RELATE ProfessorID TO ProfessorID)

It is important that the column you are relating appear both in the parent and child SQL statements. If you leave out the relating column from either SQL statement, you will get an error.

Now that we've looked at the syntax for data shaping, we still have to examine how to obtain a data shape through ADO. We'll focus on this in Part 3 of this article!

Read Part 3
(Read Part 1)