When a Normalizer transformation receives more than one type of data from a COBOL source, you need to connect the Normalizer output ports to different targets based on the type of data in each row. The following example describes how to map the Sales_File COBOL source definition through a Normalizer transformation to multiple targets.
The Sales_File source record contains either store information or information about items that a store sells. The sales file contains both types of records.
The following example includes two sales file records:
The COBOL source definition and the Normalizer transformation have columns that represent fields in both types of records. You need to filter the store rows from the item rows and pass them to different targets.
The following figure shows the Sales_File COBOL source with its corresponding Store_Data (which has a value of “S”) and Detail_Data (which has a value of “D”):
The Hdr_Rec_Type defines whether the record contains store or merchandise data. When the Hdr_Rec_Type value is “S,” the record contains Store_Data. When the Hdr_Rec_Type is “D,” the record contains Detail_Data. Detail_Data always includes four occurrences of Supplier_Info fields.
To filter data, connect the Normalizer output rows to a Router transformation to route the store, item, and supplier data to different targets. You can filter rows in the Router transformation based on the value of Hdr_Rec_Type.
The following figure shows the mapping that routes Sales_File records to different targets:
The mapping filters multiple record types from the COBOL source to relational targets. The multiple-occurring source columns are mapped to a separate relational table. Each row is indexed by occurrence in the source row.
The mapping contains the following transformations:
Normalizer transformation.
The Normalizer transformation returns multiple rows when the source contains multiple-occurring Detail_Data. It also processes different record types from the same source.
Router transformation.
The Router transformation routes data to targets based on the value of Hdr_Rec_Type.
Aggregator transformation.
The Aggregator transformation removes duplicate Detail_Data rows that occur with each Supplier_Info occurrence.
The mapping has the following functionality:
The Normalizer transformation passes the header record type and header store number columns to the Sales_Header target. Each Sales_Header record has a generated key that links the Sales_Header row to a Store_Data or Detail_Data target row. The Normalizer returns Hdr_Data and Store_Data once per row.
The Normalizer transformation passes all columns to the Router transformation. It passes Detail_Data data four times per row, once for each occurrence of the Supplier_Info columns. The Detail_Data columns contain duplicate data, except for the Supplier_Info columns.
The Router transformation passes the store name, address, city, and generated key to Store_Data when the Hdr_Rec_Type is “S.” The generated key links Store_Data rows to Sales_Header rows.
The Router transformation contains one user-defined group for the store data and one user-defined group for the merchandise items.
The Router transformation passes the item, item description, price, quantity, and Detail_Data generated keys to an Aggregator transformation when the Hdr_Rec_Type is “D.”
The Router transformation passes the supplier code, name, and column ID to the Suppliers target when the Hdr_Rec_Type is “D”. It passes the generated key that links the Suppliers row to the Detail_Data row.
The Aggregator transformation removes the duplicate Detail_Data columns. The Aggregator passes one instance of the item, description, price, quantity, and generated key to Detail_Data. The Detail_Data generated key links the Detail_Data rows to the Suppliers rows. Detail_Data also has a key that links the Detail_Data rows to the Sales_Header rows.
The following figure shows the user-defined groups and the filter conditions in the Router transformation:
The Router transformation passes store data or item data based on the record type.
ASK INFAPreview
