Handling Multivalues and Subvalues
Relational databases cannot hold sub-field information and there is no correlation in an SQL catalog with Reality multivalues and subvalues. Hence, when converting a Reality database into an SQL catalog, it may be necessary to restructure the data model in order to handle multivalues, or subvalues.
In order to restructure the data model to handle multivalues, you need to enter a code in field 6 of the column definition item associated with the multivalued attribute. This code enables the column definition to access a selected multivalue, or multivalues.
Similarly, to handle subvalues, you need to enter a code in field 7 of the column definition item associated with the subvalued attribute. This code enables the column definition to access a selected subvalue, or subvalues.
Accessing Multivalues or Subvalues in Columns
The code in field 6 of the appropriate SQL Column Definition specifies the action to be taken when the corresponding Reality attribute contains one or more multivalues. A list of codes and the actions taken are described in the table below.
The code in field 7 of the appropriate SQL Column Definition specifies the action to be taken when the corresponding Reality attribute contains more than one subvalue. The same list of codes and the actions described in the table below.
Note
Entering codes in field 6 enables access to multivalues. Entering codes in field 7 enables access to subvalues.
|
Code |
Action |
|---|---|
|
Null |
Use first multivalue or subvalue, only. |
|
0 |
Use all multivalues or subvalues, separated by spaces. |
|
n |
Use nth multivalue or subvalue (where n is a positive integer). |
|
E |
Explode - generate a separate row for each multivalue or subvalue. |
|
-1 |
Use the last multivalue or subvalue. |
Examples of Accessing Multivalued Attributes
The following Reality file item contains both multivalued and non-multivalued attributes.
|
Reality Item |
SQL Column Definitions |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
The SQL column definitions for Attributes 1 and 3 are setup for 'E'xploding values. The Reality file only contains one item. Selecting the table produces the following output:
SELECT * FROM T1
ID. . . . ATTR1 . . . ATTR2 . . . ATTR3 . . .ITM01 ABC A002123ITM01 DEF A002 456 ITM01 GHI A002 789 3 rows listed.
The inclusion of a column with exploding values means that there will be a corresponding row for each multivalue position in the Reality item. In addition, the values in all non-exploding columns are repeated on each row displayed. For example if Attribute 1 above contained "ABC]DEF]GHI]JKL]MNO", then the result displayed would have 5 rows. (that is, Number of Items multiplied by Maximum Number of Multivalues).
ID. . . . ATTR1 . . ATTR2 . . ATTR3 . . ITM01 ABC A002 123 ITM01 DEF A002 456 ITM01 GHI A002 789 ITM01 JKL A002 ITM01 MNO A002 5 rows listed.
If there are no corresponding multivalues in other multivalued attributes, then NULL values are displayed for the corresponding multivalued positions as in column 'ATTR3' above.
The table is always exploded even when selecting non-exploded columns.
SELECT ID, ATTR2 FROM T1
ID. . . .ATTR1 . . .
ITM01 A002
ITM01 A002
ITM01 A002
Examples of Handling Different Types of Multivalued Attributes
How you restructure the SQL catalog to handle a multivalued attribute depends on the type of data in the attribute. Examples of different types of multivalued data are listed below in the PERSONNEL File item.
001 Person-id 001 MR Title 002 MARK]ROBERT Forenames 003 ROBINSON Surname 004 1 HIGH STREET]STEVENAGE]SG4 1RE Address 005 MCSE]INTRODUCTION TO IMPROMPTU Qualification 006 11110]11182 Qualification date 007 MICROSOFT]COGNOS Qual. company 008 10959]9951 Post date 009 AC1]RE2 Post company code 010 10166]10023 Post Department code 011 P12]P54 Post pay code
Example of a PERSONNEL Item - Person ID 001
Note
"]" denotes a value mark.
Techniques for Handling Multivalues
The techniques used to handle these multivalued attributes all involve specifying the multivalue code in field 6 of the column definition. However, the exact technique used depends on the type of multivalued data. See the table Techniques for Handling Different Types of Multivalued Data for a list of possible techniques and associated examples.
-
Attributes 2 and 4 to 11 all contain multivalued data.
-
Attributes 2 and 4 contain multivalued data which needs to be on a single row.
-
Attributes 5 to 7 and 8 to 11 comprise sets of multivalued attributes containing related data. These attributes could be treated in a similar way to the single-row multivalues. However, a better technique is to create a 'virtual' secondary table containing only the related data. See Technique 6.
Note
-
See SQL Column and Index Definitions for a description of column definition fields.
-
Techniques 1 and 2 achieve the same result as Tfiletranslate codes, such as, A;0(PERSONNEL;X1;4;4), A;0(PERSONNEL;X2;4;4) etc. in English. It is recommended that, wherever possible, you use SQL techniques rather than English codes in order to maximise SQL efficiency.
|
Technique |
Operation |
Examples of Type of Multivalued Data |
|---|---|---|
|
1 |
Set up a column to extract the first value and ignore the rest. Enter a null in column definition field 6. |
Single row of data. |
|
2 |
Set up a column to extract the n'th value and ignore the rest. Enter an n in column definition field 6. |
Single row of data. |
|
3 |
Create a single field by concatenating all the multivalues together, separated by spaces. Enter a 0 in column definition field 6. |
Single row of data. |
|
4 |
Create a single field by concatenating all the multivalues together, separated by commas. Use the Tfile translation code in field 8 of the column definition. |
Single row of data. |
|
5 |
Expand the multivalues, one per row. Enter an E in column definition field 6. |
Related data. |
|
Multiple rows of multivalues containing data which is linked together can be handled by creating a 'virtual' secondary table in the main table to contain the data with one row per multivalue. You can do this using SQLM's Alternative View option and Technique 5. |
Examples of Handling Multivalued Data in a Single Row
Attributes 2 and 4 in the example PERSONNEL item contain multivalues that need to be displayed all in one row.
Example 1 - Attribute 2 Forenames
Attribute 2 in the PERSONNEL item contains the forenames of a person, separated by value marks. These could be handled using Techniques 1, 3 or 4. For example:
-
If only the first name is needed, set up a column to extract the first forename and ignore the rest (Technique 1).
-
If all the person's forenames are needed, concatenate the names together, separated by spaces (Technique 3).
-
Alternatively, you can concatenate the names together with a different character using the Tfile translation code (Technique 4). For example, A;0(TPERSONNEL;X:,;2;2) inserts a comma between names.
Example 2 - Attribute 4 Address
Attribute 4 in the PERSONNEL item contains the address of the person, with each part of the address separated by value marks. These can be handled in SQL using Technique 2, or in English using the Tfile translation code. For example:
-
Set up a column for each address value (for example, Address Line 1, Address Line 2, etc.) using Technique 2 to extract the required multivalue in each column definition.
-
Concatenate the address values together on one line, separated by commas (Technique 4). For example, A;0(TPERSONNEL;X:,;4;4) inserts a comma between address components.
Examples of Handling Related Multivalued Fields
Attributes 5 to 7 and 8 to 11 are two sets of multivalued attributes which contain related data. The first set (5 to 7) contains data which describes the qualifications held by the person, and when and where they where obtained. The second set (8 to 11) contains details of posts held by the person both previous and current.
The recommended method of dealing with multivalues of this type of data is to create a 'virtual' secondary table derived from the main table (Technique 6). This table will be populated by exploding the multi- or sub-valued attributes of the main table. The combination of the item-id and the multivalue (or subvalue) number can be used as the unique primary key of such a table. The example below shows how this can be achieved.
Example 3 - Attributes 5 to 7 Qualification Data
To create a 'virtual' secondary table containing the Qualifications data, proceed as follows:
-
Use SQLM option 2, Create Alternative View to create a new table definition QUALIFICATIONS from the PERSONNEL file, containing the columns:
PERSON_ID
QUALS
QUAL_DATE
QUAL_COInitially, the primary key of this table will be the same as that of its source table - PERSON_ID.
-
Use SQLM Option 4 to add an SQL column definition 'QUAL_NO' to the QUALIFICATIONS table, containing NV in the Attribute field (field 5).
The QUAL_NO column will form part of the unique primary key for the secondary table. The NV code will populate this column with the multivalue numbers from the QUALS, QUAL_DATE and QUAL_CO columns.
-
Use SQLM Option 4 to update the QUALIFICATIONS table, as follows:
-
Add the QUAL_NO to the primary key, by setting the Primary Key field in the QUAL_NO column definition to 'Y'. The Primary Key now consists of the combination of PERSON_ID and QUAL_NO.
-
Enter
Ein the Multivalue field (field 6) of the QUALS, QUAL_DATE and QUAL_CO column definitions. This will cause the multivalues in these columns to be exploded, thus creating a row in the QUALIFICATIONS table for each value.
-
Assuming the PERSONNEL file contains the data listed in PERSONNEL File, the QUALIFICATIONS table will contain the following:
|
PERSON_ID |
QUAL_NO |
QUALS |
QUAL_DATE |
QUAL_CO |
|---|---|---|---|---|
|
1 |
1 |
MCSE |
1998-06-01 |
MICROSOFT |
|
1 |
2 |
INTRO. TO IMPROMPTU |
1998-08-12 |
COGNOS |
|
2 |
1 |
IMPROMPTU ADMIN |
1998-06-09 |
COGNOS |
|
3 |
1 |
Note
There is a primary key generated for Person ID 3, even though the person has no qualifications. This is because the column exists even though it has empty strings in its Qualification fields. This can have a beneficial effect, as inner joins will still display data from this virtual secondary table for all persons.
Example 4 - Attributes 8 to 11 Post Data
You should also create a virtual secondary table for attributes 8 to 11 using a similar procedure to that followed for attributes 5 to 7. A secondary table is then created, similar to the following:
|
PERSON_ID |
POST_NO |
POST_DATE |
POST_CO |
POST_DEPT |
POST_POINT |
|---|---|---|---|---|---|
|
1 |
1 |
1998-01-01 |
AC1 |
10166 |
P12 |
|
1 |
2 |
1995-03-30 |
RE2 |
10023 |
P54 |
|
2 |
1 |
1997-06-01 |
RE2 |
10023 |
P54 |
|
3 |
1 |
1998-11-10 |
AC1 |
10166 |
P12 |