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:
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:
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:
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 |