Row List Files

Example 1

For example, assume that you want to extract specific rows from the DETAILS table using a Row List File you create using a method other than Point and Shoot. The primary key for the DETAILS table consists of two columns, ORDER_ID and ITEM_ID. These columns are defined by database:

DB2/MVS

Column Name	Data Type
ORDER_ID	DEC(5,0)
ITEM_ID	CHAR(5)

Oracle

Column Name	Data Type
ORDER_ID	NUMBER(5,0)
ITEM_ID	CHAR(5)

Sybase ASE

Column Name	Data Type
ORDER_ID	DECIMAL(5,0)
ITEM_ID	CHAR(5)

The following example shows how the list should be structured. (You can create this list using a text editor. If character data includes international characters, the text editor must be UTF-8 compatible, e.g., Windows Notepad.) For each row to be extracted, the value in the ORDER_ID column is followed by the value in the ITEM_ID column. Commas separate the values for each row, and a semicolon separates each row.

00123, 'CH001'; 00124, 'CH002'; 00125, 'CH003'; 00126, 'CH004'; 
00133, 'CH001'; 00134, 'CH002'; 00135, 'CH003'; 00146, 'CH004'; 
00153, 'CH001'; 00154, 'CH002'; 00155, 'CH003'; 00156, 'CH004'; 

Use the following data formats:

• Separate data elements using a comma followed by one or more spaces.
• Separate the primary key values for each row using a semicolon followed by one or more spaces.

Character Data

Character data must be enclosed in single quotes. Embedded quotes must be in the form of two single quotes.

Character data can be wrapped to the next line. The segments of the data must be individually enclosed in quotes without an intervening colon.

The following is an example of character data that is wrapped:

'This is an example'
'of wrapped data.'

The following is an example of character data that does not wrap (note that commas at the end of each line):

'This is an example',
'of data that does not wrap.',
'Note the use of commas.',

Character data stored in fixed length columns is truncated or padded appropriately to fit the column. Character data stored in variable length columns is truncated, as necessary, but is not padded.

Date/Time

All date and time data must be enclosed in single quotes. Any valid database format for these values is acceptable and is handled appropriately.

Numeric Data

Numeric data is not enclosed in quotes. The decimal can be indicated by either a comma or a period and is handled appropriately.

Partial Primary Key

If you want to extract non-unique values or values that do not correspond to a primary key, you can specify an alternate key or a partial primary key in your Row List File. To indicate that the data in this file contains values for some set of the columns, prefix the file with:

COLUMN-LIST

(List the names of the columns for which data is supplied)

END-COLUMN-LIST

Example 2

Assume you have a set of ITEMS rows that are not in your database. However, you want to extract the DETAILS rows from your database for specific ITEMS. The primary key for the DETAILS rows is comprised of two columns, ORDER_ID and ITEM_ID. However, you prefer to extract rows based on only the ITEM_ID. You can create a Row List File to extract the several DETAILS rows for each specified ITEM_ID value regardless of the ORDER_ID value.

COLUMN-LIST
    ITEM_ID
END-COLUMN-LIST
 'CH001'; 'CH002'; 'CH003'; 'CH004'; 
 'CH005'; 'CH006'; 'CH007'; 'CH008';
 'CH009'; 'CH010'; 

Example 3

Assume that two columns comprise the partial primary key, ORDER_ID and ITEM_ID. Specify the column values in the order in which they are listed for the column list. For each row, in this example, the values for ORDER_ID are followed by the values for ITEM_ID separated by commas. Semicolons separate the rows. Because commas and semicolons delimit each value, entries can span multiple lines, and multiple entries can be specified on a single line.

COLUMN-LIST
   ORDER_ID
   ITEM_ID
END-COLUMN-LIST
00123, ‘CH001'; 00124, 'CH002'; 00125, 'CH003'; 00126, 'CH004';
00133, 'CH005'; 00134, 'CH006'; 00135, 'CH007'; 00146, 'CH008';
00153, 'CH009'; 00154, 'CH010';