Understanding the Terminology › Repeating Fields

Repeating Fields

With CA Dataquery in DQL Mode, you can take advantage of the ability of CA Datacom/DB to duplicate the structure of a column to create another column for cases where there might be more than one occurrence of a piece of information, as in monthly figures or family dependent information. This type of data structure can be referred to as a repeating field.

Repeating Fields

          ┌──────────┬───────────────────────────────────────────────┐
          │ CUST-NO  │ MONTHLY-PAYMENT                               │
          ├──────────│───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┤
          │00039432  │ 17│ 17│ 17│ 17│ 17│ 17│   │   │   │   │   │   │
          ├──────────│───│───│───│───│───│───│───│───│───│───│───│───┤
          │00039982  │ 18│ 18│ 18│ 18│ 18│ 18│ 18│ 18│ 18│   │   │   │
          ├──────────│───│───│───│───│───│───│───│───│───│───│───│───┤
          │00044524  │ 23│ 23│ 23│ 23│ 23│ 23│ 23│   │   │   │   │   │
          └──────────┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┴───┘

In this example, MONTHLY-PAYMENT is a repeating field. There is one occurrence for each month of the current year.

A repeating field can consist of simple or compound fields. Each repetition of a repeating field is called an occurrence. Each occurrence in a repeating field can also be a repeating field. The entire set of occurrences is called an array. To refer in a query to a specific data item in an array, use a subscript, as in the following example:

PRINT FLDB(2,1)

The (2,1) denotes the second occurrence of FLDA and the first occurrence of FLDB within FLDA. See the following illustration for clarification.

Two-level Array (Repeating Field)

┌──────────────────────┬───────────────────────┬────────────────────────┐
│        FLDA          │         FLDA          │         FLDA           │
├──────────┬───────────│───────────┬───────────│───────────┬────────────┤
│    FLDB  │    FLDB   │      FLDB │    FLDB   │    FLDB   │    FLDB    │
└──────────┴───────────┴───────────┴───────────┴───────────┴────────────┘

CA Dataquery queries can access one- and two-level arrays. If an array consists of more than one level, only the first two levels can be accessed by a query, and then only if the levels are adjoining. The following examples show how to reference various types of occurrences in a simple repeating field.

Two-level Array (Repeating Field)

     ┌────────────────────────────┬──────────────────────────────┐
     │              FLDA          │            FLDA              │
     ├─────────────┬──────────────│──────────────┬───────────────┤
     │        FLD  │       FL     │        FLDB  │       FLD     │
     └─────────────┴──────────────┴──────────────┴───────────────┘

To reference in a PRINT statement:

First FLDA

PRINT FLDA(1)

Second FLDA

PRINT FLDA(2)

First FLD

PRINT FLDB(1,1)

FL

PRINT FLDB(1,2)

FLDB

PRINT FLDB(2,1)

Second FLD

PRINT FLDB(2,2)

Repeating fields can be used in any statement that permits naming a regular column or key, that is, WITH, SET, SORT, WHEN/DO.