Specifying Values for TPI Keys
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.
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 126.96.36.199, 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.
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.
'ABC Manufacturing' finds any values that match exactly.
• A relational operator and a value.
'>B' finds any values that are greater than B.
• Any of the two above combine with AND and/or OR.
:'>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
|I1, K1, J1
|I2, K2, J2
|I4, K4, J4
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.