Doing an Advanced Search

Clicking on the >>> button will show you the Advanced search options.

On this you can specify:

• other text you can search for. You can specify up to 4 pieces of text.
• when you are searching for multiple text items, whether a row can meet ANY condition or must meet ALL conditions.
• the type of search - whether values contain, start with, end with or equal the text being searched for
• the data types being searched (more on this later).

Numeric Search

You can do a numeric search - eg. search numeric columns for a particular numeric value (either equal to, less than or greater than).

You do this by entering a numeric value as a Search for text and ensuring that the numeric Column Type has been checked. If your Search for text is not numeric then numeric columns will not be searched.

How the Search works

Find Text will build an SQL statement to search each table. By default, only character columns will be searched. Cols Searched shows you how many columns in the table were searched.

• if you click on a table then View > Show Columns Searched you will be able to see which columns in the table have been searched.
• if you click on a table then View > Show Search SQL you will be able to see the SQL that AQT has built to perform the search.

Data Types

AQT can search a number of different data types - these are shown in the Search Column Types box.

Columns such as Clobs and Blobs can be searched, however the SQL for doing this is generally different for each database. To be able to search these; AQT needs to be configured with the SQL used to search these columns. For instance, to search Oracle CLOBS AQT uses: dbms_lob.instr(<colname>,'<value>') > 0. The SQL used for other databases will be different.

Find Text has been configured for a number of different data types for a number of databases. However if you have any specific requirements this can be met by adding some entries to the config file - if you need to do this get in touch with us.

Ignore Case

For most databases, the Ignore Case option is dealt with by using a lower-case function. For instance, if you are searching for Smith, AQT will use the SQL such as lcase(<colname>) like '%smith%'.

The one exception to this is Oracle. To do a case-independent search, AQT changes the session to use a case-independent collation. This is done with the command ALTER SESSION SET NLS_COMP=LINGUISTIC. When the Find Text window is closed, the session is altered to return the collation to its previous setting.