Example 1 (DataStage®)

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:

Table 1. 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:

Table 2. Output column definitions
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:

Table 3. Output data set
	Col1	Col2	Col3	Col4
row	1	AAD	Thurs	No
row	2	ABD	Thurs	No
row	3	CAD	Weds	Yes
row	4	CCC	Mon	Yes