Example 2

This example shows how the Promote Subrecord would operate on an aggregated vector of subrecords, as would be produced by the Combine Records stage.

It assumes that the job is running sequentially. The column definition for the input data set contains a definition of a single column called subrec.

The following are some rows from the input data set:
Table 1. Input data set
    subreccol subreccol subreccol subreccol
  vector index col1 col2 col3 Keycol
row 0 1 00:11:01 1960-01-02 A
  1 3 08:45:54 1946-09-15 A
row 0 1 12:59:00 1955-12-22 B
  1 2 07:33:04 1950-03-10 B
  2 2 12:00:00 1967-02-06 B
  3 2 07:37:04 1950-03-10 B
  4 3 07:56:03 1977-04-14 B
  5 3 09:58:02 1960-05-18 B
row 0 1 11:43:02 1980-06-03 C
  1 2 01:30:01 1985-07-07 C
  2 2 11:30:01 1985-07-07 C
  3 3 10:28:02 1992-11-23 C
  4 3 12:27:00 1929-08-11 C
  5 3 06:33:03 1999-10-19 C
  6 3 11:18:22 1992-11-23 C
Once the columns in the subrecords have been promoted the data will be output in four columns as follows:
Table 2. Promoted columns
Column name Key SQL type
keycol Yes Char
col1   TinyInt
col2   Time
col3   Dat

The Subrecord Column property on the Properties tab of the Promote Subrecord stage is set to 'subrec'.

The Output data set will be:
Table 3. Output data set
  col1 col2 col3 col4
row 1 00:11:01 1960-01-02 A
row 3 08:45:54 1946-09-15 A
row 1 12:59:00 1955-12-22 B
row 2 07:33:04 1950-03-10 B
row 2 12:00:00 1967-02-06 B
row 2 07:37:04 1950-03-10 B
row 3 07:56:03 1977-04-14 B
row 3 09:58:02 1960-05-18 B
row 1 11:43:02 1980-06-03 C
row 2 01:30:01 1985-07-07 C
row 2 11:30:01 1985-07-07 C
row 3 10:28:02 1992-11-23 C
row 3 12:27:00 1929-08-11 C
row 3 06:33:03 1999-10-19 C
row 3 11:18:22 1992-11-23 C