Functions

OAJOIN

oajoin qualifies rows in a table using column values of rows previously qualified by oaqualify in another table, and returns a count of the qualified rows. This provides joined access to data across tables.

oajoin qualifies rows in an OMNIDEX indexed table that is related to another OMNIDEX indexed table by some column. It does this by using the values in the from_col of qualified rows of from_table to search against to_col of to_table.

These are the requirements for oajoin:

• From_table must be the table referenced in the most recent successful oaqualify or oajoin for that cursor.
• From_col must exist in from_table.
• To_col must be a valid column in to_table and a non-parsed, non-grouped OMNIDEX index.

or

• To_col must be the link column that links to_table to from_table.
• For OMNIDEX Client 2.0 and later, from_table must be in the domain that corresponds to the parent table referenced in the to_owner parameter.

Using Multi-Domain Children

When a child table belongs to multiple domains, it is called a multi-domain child. oajoin lets you exploit a child table’s links by performing bitmap joins between domains. A bitmap join substitutes the OMNIDEX ID of the parent table in the second domain for the OMNIDEX ID of the parent table in the first domain.

Although you can also perform this qualification with single domain child tables that have a common linking column to another parent table, multiple-domain children perform this qualification much more quickly.

To perform a multi-domain qualification, first qualify rows in a domain. Then call oajoin, passing:

• the name of the multiple-domain child table in the from_table and to_table parameters
• the name of the parent table in the domain to which you want to switch in the to_owner parameter
• the name of the column linking the child table to the first domain in the from_col parameter
• the name of the column linking the child table to the second domain in the to_col parameter

This oajoin substitutes the OMNIDEX ID of the second domain’s parent table for the OMNIDEX ID of the first domain’s parent table. You must then call oajoin a second time to join to the parent table of the second domain if you want access to those parent rows for reporting.

For example, you have an ORDERS table linked to both CUSTOMERS and PRODUCTS domains. After you have qualified rows using ORDERS in the CUSTOMERS domain, you can then call oajoin to bitmap join to the PRODUCTS domain.

oajoin( cursor1, ";", status, "ORDERS;", "CUSTOMER_NO;", "PRODUCTS;", "ORDERS;", "PRODUCT_NO")

You then call a second oajoin to link to the PRODUCTS table:

oajoin( cursor1, ";", status, "ORDERS;", "PRODUCT_NO;", ";", "PRODUCTS;", "PRODUCT_NO")

You can then call oaselect to retrieve rows from the table in the second domain. If the native management database system is relational, you can oaselect against multiple tables in the second domain.

The Qualified Subset

If a call to oajoin is successful, the existing qualified subset of rows is converted to reflect the related rows in to_table. This creates a new qualified subset that you can further qualify by calling oaqualify

If there are no rows related to the existing qualified subset, either of two things can happen:

If the AUTORESET option was used and oajoin does not qualify rows in to_table, the qualified subset of rows still reflects the original rows qualified in from_table.

By default, or if the NOAUTORESET option was used, and the oajoin does not qualify rows in to_table, the qualified subset of rows is set to a null.

For some applications, especially batch reports, it is desirable to abandon the retrieval if a join qualifies no rows. Such applications should call oajoin with the NOAUTORESET option, which sets the qualified subset to null if a search does not qualify any rows.

The UNDO Option

The UNDO option backs out of the most recent successful oajoin and restores the qualified subset of rows that immediately preceded it. To restore the qualified subset of rows to the way it was before a join, call oajoin with the UNDO option.

You can only use the UNDO option once in succession. If you try to call oajoin with the UNDO option several times in succession, oajoin returns an error. This means that you can only back out of, or undo, one oajoin (the most recent) in a series of successful oajoin operations.

When you use the UNDO option, the from_table, from_col, to_table and to_col parameters are ignored. However, you must still pass a pointer to a valid address. Passing a null value for any of the parameters returns an error.

Syntax

oajoin (cursor, options, status, from_table, from_column,
to_owner, to_table, to_column)

cursor -- Is a 32-bit signed integer passed by value. This is the value returned by a successful call to oaopencursor

options -- Is a 256-byte character string, passed by reference and terminated by a semicolon or a null character. It indicates the action(s) for oajoin to take.

status -- Returns information about the success or failure of the oajoin routine. A zero status.error means a successful call to oajoin. The status structure is passed by reference and contains fourteen 32-bit signed integers, followed by a 36-character buffer.

from_table -- The name of the table to join to another table. From_table must be the table specified in the most recent oaqualify,or the to_table specified in the most recent oajoin, for that cursor. From_table is a character value passed by reference, not longer than 33 bytes including a semicolon or null terminator.

from_col -- The name of the column within from_table that establishes a relationship with to_table. From_col is a character value passed by reference, not longer than 33 bytes including a semicolon or null terminator.

to_owner -- The name of the domain to join to as expressed by the domain’s parent table. To_owner is a character value passed by reference, not longer than 33 bytes including a semicolon or null terminator. This parameter is new for OMNIDEX versions 3.4 and later.

to_table -- The name of the table to join to. To_table is a character value passed by reference, not longer than 33 bytes including a semicolon or null terminator. To_table must be part of an OMNIDEX domain passed in the to_owner parameter.

to_column -- The name of the column within to_table to join to. To_col is a character value passed by reference, not longer than 33 bytes including a semicolon or null terminator. To_col must be a non-parsed, non-grouped OMNIDEX index, unless to_col is the link column, specified during OMNIDEX installation, that prejoins from_table to to_table. To_col can be any non-parsed keyword index, or any keyword composite index as long as it’s not of mixed data type.

Options

COUNT -- returns a count of the rows in to_table that correspond to the rows in from_table. This is the default in joins across domains.
COUNT is required when joining from a parent table to continue a search using indexes in a child table that were not installed with the Record Complex option.

NOCOUNT -- does not return a count of the rows in to_table that correspond to the rows in from_table if the count would impact performance. This is the default on joins within a domain.

AUTORESET -- automatically preserves the previous subset of qualified rows if the join doesn’t qualify any rows.

NOAUTORESET -- sets the subset of qualified rows to null if the join doesn’t qualify any rows. This is the default.

UNDO -- restores the qualified subset of rows to the way it was before the last successful oajoin.

Passing a semicolon or null in the options parameter causes oajoin to use the NOCOUNT option for joins within a domain. A count is returned for all joins across domains.

Example

Top