LOOKUP stage v1.0

Pipelines v1.9

Purpose, Operands, Streams used, Usage notes, Examples, See also

Home

Syntax

 
 _NOPAD_____
>>__LOOKUP__ _______ __|___________|____________________________________________________>
 |_COUNT_| |_PAD_char__|
 
 
 _1-*_____________1-*____________ _DETAILs MASTER_
>__|________________________________|__|________________|__ ______ ____________________><
 |_columnrange1__ ______________ _| |_DETAILs________| |_BOTH_|
 |_columnrange2_| |_MASTER_________|
 |_MASTER DETAILs_|
 
Notes:
(1) columnrange1 and columnrange2 are unsigned.
 

Purpose

Use the LOOKUP stage to find records in a reference.

LOOKUP matches records in its primary input stream with records in its secondary input stream and writes matched and unmatched records to different output streams. The records are matched on the basis of a key field (the contents of a specified range of columns). LOOKUP reads records from its primary and secondary input streams and writes records to its primary, secondary, and tertiary output streams, if each is connected. The secondary input stream must be defined and connected.

The records in the secondary input stream are the master records. LOOKUP first reads the master records into a buffer, where records with duplicate key fields are discarded; the first occurrence of a key field is retained. The records in the buffer are referred to as the reference. The records in the primary input stream are the detail records. LOOKUP compares detail records to records in the reference.

LOOKUP writes records to three output streams, if each is connected:

●	The primary output stream contains matching records. You can specify the sequence of the master and detail records written to the primary output stream; both detail and master records, only detail records, or only master records.
●	The seondary output stream contains detail records that do not have a matching master record.
●	The tertiary output stream contains master records in ascending order by their key fields.

Operands

●

COUNT

specifies that each master record is prefaced with a 10-character field that represents the number of detail records that match the master record. The 10-character field is right-aligned with leading spaces.

If you specify COUNT and the tertiary output stream is connected, all master records written have a 10-character prefix that contains the number of matching detail records; unmatched record have a count of 0. If you do not specify COUNT and the tertiary output stream is connected, only unmatched master records are written.

●

NOPAD

specifies that shorter key fields are not extended with a pad character before they are compared with longer key fields in other records. This is the default.

●

PAD

specifies that shorter key fields are extended with a pad character before they are compared with longer key fields in other records.

●	char is the pad character.

●

columnrange1

is an unsigned integer column range which defines a key field for the detail records. If columnrange1 is not specified, the key field is the entire record for both the primary and secondary input streams. The format of columnrange1 is identical to the format specification for columnrange2.

●

columnrange2 is an unsigned integer column range which defines a key field for the master records. If columnrange2 is not specified, columnrange1 defines the key field for both the primary and secondary input streams. You can specify columnrange2 (which is identical to the format specification for columnrange1).

●

DETAILs MASTER

specifies that the matched detail records followed by their master record are written to the primary output stream. This is the default.

●

DETAILs

specifies that only the detail records are written to the primary output stream. The matched master records are discarded.

●

MASTER

specifies that only the master records are written to the primary output stream. The matched detail records are discarded.

●

MASTER DETAILs

specifies that the master record followed by its matching detail records are written to the primary output stream.

●

BOTH

when you specify that matching master and detail records should be written to the primary output stream, the BOTH operand specifies that both records are written every time a match occurs.

Streams used

The following streams are used by the LOOKUP stage:

Stream	Action
Primary input stream	After reaching end-of-file on its secondary input stream, LOOKUP reads detail records from its primary input stream.
Secondary output stream	LOOKUP reads master records from its secondary input stream. The secondary input stream must be connected.
Primary output stream	LOOKUP writes matching records to its primary output stream.
Secondary output stream	LOOKUP writes detail records that do not have a matching master record to its secondary output stream, if it is connected.
Tertiary output stream	LOOKUP writes master records in ascending order by their key fields to its tertiary output stream, if it is connected. If the COUNT operand is specified, all master records are written. Otherwise, only the unmatched master records are written.

Usage notes

1)	LOOKUP does not delay its primary input stream records. LOOKUP delays its secondary input stream records.
2)	If the LOOKUP stage discovers that all of its output streams are not connected, the LOOKUP stage ends.
3)	The following diagram shows the input and output streams for the LOOKUP stage using the default operands:
4)	Although the COLLATE and LOOKUP stages both process master and detail records read from their input streams, COLLATE reads master records from its primary input stream and LOOKUP reads master records from its secondary input stream. COLLATE reads detail records from its secondary input stream and LOOKUP reads detail records from its primary input stream.
5)	LOOKUP verifies that its tertiary input stream is not connected and then begins execution.

Examples

The pipeline in the following example shows how to specify a LOOKUP stage as shown in the preceding diagram. The LOOKUP stage reads records from its primary and secondary input streams and writes the contents of its primary, secondary and tertiary output streams to separate files.

pipe (endchar ? comment //)
 < details.txt // Read detail records.
 | l: LOOKUP // Find matches.
 | > matchingrecords.txt // Write matching masters and details.
 ? // Start of second pipeline.
 < master.txt // Read master records.
 | l: // Define secondary stream for LOOKUP.
 | > unrefdetails.txt // Write details without masters.
 ? // Start of third pipeline.
 l: // Define tertiary stream for LOOKUP.
 | > unrefmasters.txt // Write details without masters.

See also

Reference the following link for additional information:

COLLATE