Given the following situation:
We have a root file. The records in the root file can each own up to
99999 records in file A. The records in A can each own any number of
records in file B, so long as there are no more than 99999 B records for
any given root record. The records in B can own records in C, but again,
no more than 99999 per root record, and so on to any level of nesting,
but always with the constraint of no more than 99999 of a given level
sub-record per root record.
Each A, B, C, . . . record is keyed to the root record, and to a 5-digit
serial number within the root record; each B, C, . . . record is also
keyed to its parent's serial number (in A, the "parent serial number"
field is present, but always zero).
Now consider a new database design: each A, B, C, . . . record has a
long serial number as its physical file key, a field pointing to its
immediate parent's key, and a logical keyed to the parent and the serial
number. In the case of A, the "immediate parent" is the root record's
key. In the case of B, the "immediate parent" is the serial number of
the A record that owns it. In the case of C, it's the serial number of
the B record that owns it, and so forth as needed.
Within any given instance of this situation, the structure of the root
file remains unchanged. Likewise, within any given instance, the
structure of the meaningful data in A, B, C, . . . remains unchanged
from the old design to the new, but the old key fields are replaced with
new ones. But different instances may have different amounts of nesting
(from never getting past the A level, to theoretically going as far as
Z), and always differ in what the meaningful data is, and how it's
Obviously, we can convert from the old design to the new design using
native record level access, but this requires a complete new program to
be written to accommodate every instance.
Is there a way to do the conversion using SQL, that would avoid having
to write a different program for every instance?
James H. H. Lampert
This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact