Row List Files

Point and Shoot allows you to select individual rows (primary key values) from a Start Table to begin Extract Processing. These selected rows are stored in a Point and Shoot File. However, if you need to extract values from data that does not reside in a database table or resides in a database that is not easily accessible, you can create a Row List File manually or by using a utility appropriate for your data source.

If you create a Row List File manually, the file must conform to the file format generated using Point and Shoot. The following guidelines apply:

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:

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

Note: The order of column names in the list indicates how the column data is to be processed.

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'; 

Using the List

Use the following steps to specify your Row List File in an Extract Request:

  1. In the Actions menu, select Extract.
  2. In the Tools menu, select Edit Access Definition.
  3. On the Point and Shoot tab, select File and specify the name of the Row List File.

The file you name is used to extract rows from the Start Table when the Extract is performed.