MERGELIST()

The MERGELIST() function combines two field mark delimited lists, returning the intersection, union or difference as a result list.

Format

MERGELIST(list1, list2, modes)

where

list1, list2

are the two lists to be combined. These may be strings or select list variables.

modes

identifies the operation to be performed and the manner in which the lists are sorted.

The MERGELIST() function provides an efficient way to merge two field mark delimited lists. It is intended for use with lists of record ids such as select lists but can combine other lists where the elements are no more than 255 characters. Unless the ML$UNSORTED option is present, list1 and list2 must be in sorted order.

The modes argument must be one of the following token that are defined in the SYSCOM KEYS.H include record:

ML$INTER

Return a list of items in both list1 and list2.

ML$UNION

Return a list of items in either list1 and list2 but not duplicating items in both lists

ML$DIFF

Return a list of items in list1 but not in list2.

In addition, one of the following tokens may be added to the modes value to indicate how the data in list1 and list2 is sorted:

SRT$LEFT

A simple left aligned sort (default).

SRT$RIGHT

A simple right aligned sort, sorting integer numeric values as numbers.

SRT$COMPOUND

A compound sort.

SRT$RIGHT.FLOAT

A right aligned sort, sorting both integer and floating point values as numbers.

The additive ML$UNSORTED modes value indicates that the lists to be merged may not be in sorted order. In this case, the lists will be sorted in the manner specified by use of the SRT$xxx tokens prior to merging. If the ML$UNSORTED mode is not present and the supplied lists are not in the specified sort order, the effects of the MERGELIST() function are indeterminate.

For more information on the different sort modes, see the SORT() function.

The list to be sorted may be strings or select list variables. The result returned by the MERGELIST() function is always a field mark delimited dynamic array. It can be converted to a select list using FORMLIST().

Example

SELECTINDEXV 'CUSTNO', CUSTOMER FROM ORDERS.F TO CUST.ORDERS

SELECTINDEXV 'MONTH', ORDER.MONTH FROM ORDERS.F TO MONTHLY.ORDERS

JULY.ORDERS = MERGELIST(CUST.ORDERS, MONTHLY.ORDERS, ML$INTER)

The above example extracts data from two indices; one for all orders placed by a specific customer, the other for all orders placed in a specific month. It then uses MERGELIST() to combine these to give a list of all orders placed by that customer in the given month. Because SELECTINDEXV returns the index values in sorted order, no additional sorting is required.