Introduction

The functions described in following sections provide efficient lookup features to retrieve selected information from other tables (referred to as lookup tables) which will then be written into the target table. All function varieties follow the same basic lookup principle in similar ways: For the target table, one or more input columns need to be specified where the corresponding contents in every row will be compared with the search columns in the lookup table. When a matching row has been found, then the corresponding contents in the output columns in the lookup table will be transferred to the destination columns in the target table. B4P provides following function families:

table lookup	Information found in the lookup table will be retrieved and written into the target table.
table integrate	Information found in the lookup table will be combined with the existing contents in the target table, using underlying rules of the specified table integration operation identifiers. Example: Retrieved numeric scores can be added to the existing value in the destination colum, instead of simply overwriting them.
table expand	Works similar to table integrate, but if two or more matches are identified in the lookup table, then additional rows will be inserted in the target table, original contents repeated downwards in the target table, and then updated with the contents retrieved from the lookup table.
table expand fast	A variant where the additional rows are added to the end of the table in order to avoid wasting time shifting other rows downward
table digest	Works similar like table integrate where all matches instead of just the first one in the lookup table are integrated into the target table.

These functions come with following variants where these variants are added to the function names above:

... top down ...	The table will always be searched from top to bottom. No automatic indexing made for large lookup tables. Beneficial for lookup tables where the first rew rows at the top would be found most frequently in order to save search time.
... fast ...	The row number of the last match in the lookup table will be memorized. The search continues there when the search begins with the next row in the target table. Very useful if both target table and lookup table are already in an alphabetic order and this can accelerate the lookup performance significantly. Not applicable for table digest().
... once ...	Every row in the lookup table can be retrieved only once. For the next search, the next match below that row would apply, as long there are matches.
... smart ...	The lookup table contains smart patterns supporting comparison operators, wildcareds, multiple values and ranges. In addition, B4P expressions returning boolean results can be specified.
... smart once ...	Combination of both once and smart.
... with rules ...	An opposite variant of smart: The matching patterns and possibly B4P expressions are specified in the target table and the lookup table contains the contents compared with and returned where matches are identified.
... with rules once ...	Combination of both once and smarty/b>.

Finally, the function names can be rounded up with the ... ignore case suffix which makes all comparisons case insensitive. Only exception: B4P expressions returning boolean results are not affected by this rule, but the ignore-case ruling can be included in the expression, for example using the '+' comparison options.

---

Procedures and Functions Provided:

Looking up data from other tables:
    table lookup
    table lookup ignore case
    table lookup top down
    table lookup top down ignore case
    table lookup once
    table lookup once ignore case
    table lookup fast
    table lookup fast ignore case
    table lookup smart
    table lookup smart ignore case
    table lookup smart once
    table lookup smart once ignore case
    table lookup with rules
    table lookup with rules ignore case
    table lookup with rules once
    table lookup with rules once ignore case

Looking up data from other tables and integrating their results:
    table integrate
    table integrate ignore case
    table integrate top down
    table integrate top down ignore case
    table integrate once
    table integrate once ignore case
    table integrate fast
    table integrate fast ignore case
    table integrate smart
    table integrate smart ignore case
    table integrate smart once
    table integrate smart once ignore case
    table integrate with rules
    table integrate with rules ignore case
    table integrate with rules once
    table integrate with rules once ignore case

Looking up data from other tables, integrate results, add rows in case of multiple matches:
    table expand
    table expand ignore case
    table expand once
    table expand once ignore case
    table expand fast
    table expand fast ignore case
    table expand fast once
    table expand fast once ignore case
    table expand smart
    table expand smart ignore case
    table expand smart once
    table expand smart once ignore case
    table expand fast smart
    table expand fast smart ignore case
    table expand fast smart once
    table expand fast smart once ignore case
    table expand with rules
    table expand with rules ignore case
    table expand with rules once
    table expand with rules once ignore case
    table expand fast with rules
    table expand fast with rules ignore case
    table expand fast with rules ignore case once
    table expand fast with rules once

Looking up data from other tables, Integrate results from one or more matching row into target table row:
    table digest
    table digest ignore case
    table digest once
    table digest once ignore case
    table digest smart
    table digest smart ignore case
    table digest smart once
    table digest smart once ignore case
    table digest with rules
    table digest with rules ignore case
    table digest with rules ignore case once
    table digest with rules once
    table describe
    table describe ignore case
    table describe selected rows
    table describe selected rows ignore case

Merge two tables with union-set principle:
    table merge
    table merge exclusive columns
    table merge extend columns
    table merge intersect columns
    table merge subtract columns

Table overlays the other table:
    table overlay
    table overlay columns
    table overlay exclusive columns
    table overlay extend columns
    table overlay intersect columns

Row-wise intersection made from two tables:
    table intersect
    table intersect columns
    table intersect exclusive columns
    table intersect extend columns
    table intersect intersect columns

One table does row-subtraction on other table:
    table subtract
    table subtract columns
    table subtract exclusive columns
    table subtract extend columns
    table subtract subtract columns

Derive disjoint union from both tables:
    table exclude
    table exclude columns
    table exclude exclude columns
    table exclude exclusive columns
    table exclude extend columns

Relational multiplication of two tables:
    table multiply
    table multiply selected rows

Relational division of two tables:
    table divide
    table divide selected rows

Arrange multiple tables side-by-side:
    table arrange
    table arrange with spacing