The COMPLEXTABLE function searches the specified complex table for a single record and returns a single field from that record. The parameters to this function include, at a minimum, the name of the complex table to be searched and the value to search for within the records. If only these two values are provided, the function will search the complex table using the table’s primary index, returning the field upon which the primary index has been defined from the record found.
As optional parameters to the function, the index to search upon and the field to return from the matching record can be specified by passing in the definition names of each. While optional, these parameters are provided in most use cases.
An additional variation on the parameters passed to the function is in the situation when a search index is specified, and that index is a child to another index within the table. In this scenario, additional search values must also be provided to the function. The requirement is for each index, starting with the one specified up to the top-level index (one that has no parent index) in the structure, there must also be a corresponding search value provided to the function.
As an example, assume a complex table with three indexes defined: A, B, and C. Index C is a child index to B, and B in turn is a child index to A, which is a top-level index in the table. When searching this table with the COMPLEXTABLE function, if index C is specified as the search index, search values for indexes A, B, and C must be provided. During the search, the function will begin by finding records that match on index A, then within that set those records that match index B, and finally within that sub-set the first record that matches index C.
(Table Name, Search Value, [Parent Search Value N,] [Search Index,
- Table Name Required string parameter; contains the name of the complex table to be searched by the function.
- Search Value Required string parameter; contains the value used to search the complex table records.
- Parent Search Value N String parameter(s); required and specified only when Search Index is a child index. One Parent Search Value must then be specified for each index above the Search Index in the index hierarchy.
- Search Index Optional string parameter; provides the name of the index within the complex table used by the function to locate the desired record based on the Search Value and possibly Parent Search Values. If this parameter is not provided, the primary index of the complex table is used by the function.
- Return Field Optional string parameter; provides the name of the complex table field whose value is returned from the record found by the function. If this parameter is not specified, the field for which the primary index of the complex table is defined is the default field value returned.
Supported Return Types
- Integral Number
- Decimal Number