ODBC Specifying Values for TPI Keys

Upgrade Notes

When upgrading to newer versions, you may need re-create the links to your datasets. This step is required to pass the key information to the client application where it is stored.

Use of TPI key data

All the TPI keys appear read-only items in the table. Do not use this data. The TPI fields are represented in SQL so that they are available as the objects of comparisons. Take care not to use the TPI key as the source of data for a comparison.

 Using TPI keys in JOIN statements

Use of TPI key items in the data portion of comparisons is not supported. What this means is on the left side of an equal sign, the data returned by the TPI key item may NOT be what is expected when used as part of a join. When the TPI key is used for a selection criteria compared to some constant value, it may appear on either side of the comparison operator.

For example:

tablea.item1 = Image X6
tablea.item2 = TPI X6
tableb.item1 = Image X6
tableb.item2 = TPI X6

These work well:

select … from tablea, tableb where tablea.item1 = tableb.item2

select … from tableb, tablea where tableb.item1 = tablea.item2

These may not work:

select … from tablea, tableb where tablea.item2 = tableb.item2

select … from tableb, tablea where tableb.item2 = tablea.item2

Additionally; the order of tables in the from clause is important to any selection criteria in the where clause.

For example this is fast as only qualified records from tablea are joined to records from tableb:

select … from tablea, tableb where tablea.item1 = tableb.item2 and tablea.item2 = ‘ABC’

For example this is potentially slow as first all the records from tablea are selected and joined to records from tableb then they are qualified:

select … from tablea, tableb where tablea.item1 = tableb.item2 and tableb.item2 = ‘ABC’

All TPI keys are now shown as read-only items

If a TPI key has the same name as a TurboImage item, the TPI key item name will have “_TPI” appended to it. In order to cause ODBC to find records using a TPI key you must specify the TPI key item in the WHERE clause. In versions previous to 2.1.1.1, you specified the TurboImage item that was indexed by the TPI key in the WHERE clause. Doing this will no longer cause ODBC to use the TPI key to find records, but instead will resort in ODBC doing a serial read of the table.

You must update your WHERE clauses on existing statements to use the TPI key, instead of the TurboImage item indexed by the key.

 Example selection criteria for TPI keys

For X and U type TPI keys

• If the key is a generic type key and the relational operator is =, the value of the selection criteria may contain the exact value or a value containing the @, ?, and # wildcard characters. The @ wildcard character is required to be at the end of the value.

For example:

UNITED@ finds any values starting with UNITED.

• If the key is a generic type key you may also use the following relational operators: >, >=, <, <=”,” and LIKE.

• If the key is a multiple-key type of key and the relational operator is =, the value of the selection criteria may contain:

• A value only.

Example:
‘ABC Manufacturing’ finds any values that match exactly.

• A relational operator and a value.

Example:
‘>B’ finds any values that are greater than B.

• Any of the two above combine with AND and/or OR.

Example
:’>C’ finds any values greater than or equal to B and less than C.

Note: The value must immediately follow the relational operators (no spaces between them).

• If the key is a multiple-key type key you may also use the following relational operators:>=B AND ‘>=B AND, >=, <, <=”,” and LIKE.

• If the key is a keyword type key, the value of the selection criteria may contain any word to be searched for. It may also contain the @, ?, and # wildcard characters. The @ wildcard character is required to be at the end of the value. You may also use the LIKE relational operator.

 For numeric type items

You may use the following relational operators: =, >, >=, <, <=”,” and BETWEEN. The selection criteria may only contain the value to search for.

Selection criteria for composite keys

Composite keys are always represented as X types. If all of the items that make up a composite key are X or U types than the composite keys value is the concatenation of its composite items including any trailing or leading spaces. If any item of a composite key is not an X or U type, then the items value must be represented as a X type. The following shows how ODBC represents composite items of various types:

The value may contain a plus or minus sign, and must always be right justified and left padded with zeros. For example a composite key containing two items, where the first item is a J2 with the value of 179210 and the second is a X10 with the value of 1 ABC St, would have a value of 000001792101 ABC St.