Example 1
In this example the Promote Subrecord stage promotes the records of a simple subrecord to top level columns.
It extracts data from a single column containing a subrecord. The data is output in four columns, each carrying a column from the subrecord. The example 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 the rows from the input data set:
subrec | |||||
---|---|---|---|---|---|
subrecord column name | col1 | col2 | col3 | col4 | |
row | 1 | AAD | Thurs | No | |
row | 2 | ABD | Thurs | No | |
row | 3 | CAD | Weds | Yes | |
row | 4 | CCC | Mon | Yes | |
5 | BDD | Mon | Yes | ||
6 | DAK | Fri | No | ||
7 | MDB | Tues | Yes |
The stage outputs the data it extracts
from the subrecord in four separate columns of appropriate type. The
output column definitions are as follows:
Column name | SQL type |
---|---|
col1 | TinyInt |
col2 | Char |
col3 | Char |
col4 | Char |
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 | AAD | Thurs | No |
row | 2 | ABD | Thurs | No |
row | 3 | CAD | Weds | Yes |
row | 4 | CCC | Mon | Yes |