Troubleshooting
Problem
This technote explains how to create a case insensitive query with IBM® Rational® ClearQuest® when using Oracle as the database vendor. This solution is not applicable when using Microsoft® SQL Server or Microsoft Access as the database vendor.
Cause
ClearQuest initiates queries against databases without specifying preference to case sensitivity. When a back-end database has been setup to be case sensitive, ClearQuest does not provide a method for running a case insensitive query using the query editor.
Resolving The Problem
Control of case-sensitivity for queries of multiline text fields on Oracle 10g databases
The ClearQuest 7.0.1 introduced a method for creating indexes that allow case insensitive searches on Oracle 10g databases and the CLOB datatype. For details on enabling Oracle 10g case insensitive searches, refer to the following whitepaper on the IBM developerWorks site:
http://www.ibm.com/developerworks/rational/library/07/0531_clearquest/
In IBM Rational ClearQuest environments using Oracle version 10g or later, administrators can use a feature in ClearQuest version 7.0.1 to allow users to run queries on multi-line text fields and return case-insensitive results.
Case insensitive searches with Oracle 8 and 9
There is a workaround for Oracle 8 and 9 databases that use the LONG datatype and have the Intermedia Context server enabled for multiline text fields.
Note: This solution does not work with Oracle 10g or the CLOB datatype.
Use the ClearQuest SQL Editor to modify the query by adding the LOWER function around the field names.
Example syntax:
LOWER(T1.fieldname)}
The ClearQuest SQL Editor is initiated by choosing View > SQL Pane within the ClearQuest native client. A separate tab will appear beneath the Result Set with the label SQL editor which will allow modifications of the SQL.
Note: When the SQL is modified using the SQL Editor, it is no longer possible to use the GUI tools to change display/filter information. It must be modified manually from then on.
Example of a Working Query. . .
select distinct T1.dbid,T1.headline,T1.id from defect T1 where
T1.dbid <> 0 and LOWER(t1.headline) LIKE '%another%'
Was this topic helpful?
Document Information
Modified date:
04 December 2018
UID
swg21130205