OUTERJOIN()

OUTERJOIN()

Top  Previous  Next

 

The OUTERJOIN() function returns the record ids of records in a file where a field holds a specified value.

 

 

Format

 

OUTERJOIN({DICT} file.name, field.name, value)

 

where

 

file.nameevaluates to the name of the file from which data is to be retrieved. The optional DICT prefix specifies that the dictionary portion of the file is to be used. Alternatively, the file.name expression may include the uppercase word DICT before the actual file name and separated from it by a single space.

 

field.nameis the name of the field in file.name that determines the record ids to be returned. There must be an alternate key index on this field.

 

valueis the value that must appear in the specified field.

 

 

The OUTERJOIN() function uses an alternate key index on field.name to return a value mark delimited list of record ids of records in the specified file that contain the given value.

 

This function is mainly intended for use in dictionary I-type expressions where the equivalent programming built around SELECTINDEX cannot be used.

 

 

Examples

 

OUTERJOIN('ORDERS', 'CUST.NO', CUST.NO)

 

The above expression used in a dictionary I-type item retrieves a list of orders record ids for a given customer.

 

 

OPEN 'CUSTOMERS' TO CUS.F ELSE STOP 'Cannot open file'

SELECT CUS.F

LOOP

  READNEXT ID ELSE EXIT

  DISPLAY ID:': ':CHANGE(OUTERJOIN('ORDERS', 'CUST.NO', ID), @VM, ', ')

REPEAT

 

The above program displays a list of customer numbers in the CUSTOMERS file and a list of the orders placed by each customer.