Configuring Case and Trailing Space Behavior for Queries
Query options allow you to control the case and trailing space behavior for an individual query, but can produce unpredictable results when numerous data sources are used with varying case-sensitivity and trailing-space settings.
The query options are CASE_SENSITIVE and IGNORE_TRAILING_SPACES, which can be used with SELECT, INSERT, UPDATE, and DELETE. For descriptions, see the “TDV Query Engine Options” topic of the TDV Reference Guide.
Note: These query options override TDV configuration parameter settings.
For example, you submit the following SQL statement:
SELECT v1.balance FROM accounts v1
WHERE v1.account_name = 'bob'
If TDV submits this syntax to a case-sensitive database, TDV expects to get only accounts with lowercase 'bob' as the name. If TDV submits this syntax to a case-insensitive database, TDV expects to get accounts with ‘bob’ in any combination of uppercase and lowercase letters.
If you know the database is case sensitive and you want a case-insensitive comparison, submit:
SELECT v1.balance FROM accounts v1
WHERE UPPER(v1.account_name) = UPPER('bob')
The same is true of TDV. However, if TDV is not case sensitive and the underlying database is case sensitive, TDV adds the UPPER function to the SQL sent to the underlying database.
Note: Doing this invalidates any existing index and therefore requires a new table scan, which might affect performance.
To configure case and space using SQL query options
1. Determine what query is having the most effect on performance.
2. Use SQL query options to override the configuration settings for that query.