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.

Multivalue and Subvalue Codes in Column Definition Fields 6 and 7

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

ITM01

ID

001 ABC]DEF]GHI

ATTR1

002 A002

ATTR2

003 123]456]789

ATTR3

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 A002 123
ITM01 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.

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.

Techniques for Handling Different Types of Multivalued Data

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.
See Example 1, sub-paragraph 1.

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.
See Example 2, sub-paragraph 1.

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.
See Example 1, sub-paragraph 2.

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.
See Example 1, sub-paragraph 3 and Example 2, sub-paragraph 2.

5

Expand the multivalues, one per row. Enter an E in column definition field 6.

Related data.
See Example 3 (step 3) and Example 4.

6

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.

Related data.
See Example 3 and Example 4.

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:

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:

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:

  1. 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_CO

    Initially, the primary key of this table will be the same as that of its source table - PERSON_ID.

  2. 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.

  3. 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 E  in 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