Array Handling

When you enter or load column definitions containing arrays, the Complex file load option dialog box appears. This dialog box offers the following options:

• Flatten selective arrays. Allows you to select arrays for flattening on an individual basis. Right-click an array in the columns list and select Flatten or Normalize. The array icon changes for the arrays that you select to flatten.
• Flatten all arrays. Creates new columns for each element of the array and presents the data as one row at execution time.
• Normalize all arrays. Presents the data as multiple rows at execution time with one row for each column in the array. This is the default.

Array columns that have redefined fields or OCCURS DEPENDING ON clauses cannot be flattened. Even if you choose to flatten all arrays in the Complex file load option dialog box, these columns are always normalized. Only the used occurrences of ODO columns are output from the stage.

If a record contains zero occurrences of an ODO column, one row will be processed. The value used for the elements that were subject to the ODO clause will be null if the element is nullable, or space or zero (depending on data type) it the element is not nullable.

The Complex file load option dialog box does not appear if ODOs are the only type of array column in your file. If both types of array columns are present, the dialog box appears but ODO columns are not available for selective flattening.