Limitations on cross-table join queries

The new implementation of the Cross Table Join mechanism has the following limitations and behavior changes:

• You can only run a query on the eight functions that are described in Cross-table join functions
• The DISTINCT clause is not supported.

• You cannot run a join operation on the following:

  • BLOB or CLOB objects (This is a limitation common to most RDBMSs)

  • Image data-types
  • Text data-types

  • Encrypted fields

  • LDAP files

  • Joined files
  • Merged files
  • Tables in different RDBMSs

• Queries on IR files now behave differently. Specifically, the following behaviors now occur:

  • Full text search no longer stems. The revised mechanism uses an SQL LIKE clause to retrieve results. Therefore, a search based on LIKE "%printer%" will no longer return stemmed variants such as "printing" or "printed." In the previous implementation, stemming was included because the query was run entirely in Service Manager and leveraged the stemming dictionary.

  • You can no longer perform a full text search in the Cross Table Query in SM. Full-Text Search can only be performed in the initial relational search.

  • Results are returned only from the exact fields\ that you specify. For example, if you searched for the term "printer" on the "Title" field by using the previous mechanism, you would receive a hit even if the term was not mentioned in the "Title" field, but only in the associated "Desc" field. With the new mechanism, you will only receive a hit if the term appears in the "Title" field.

• You can no longer GROUP or SORT by BLOB objects.