Search Table

The Search Table window provides a quick way or building a simple search on a table. It can be invoked in one of the following ways:

from the Database Explorer, clicking on Table > Search Table (Ctrl+Shift+F5)
from the Run SQL window, clicking on Table > Search Table (Ctrl+Shift+F5)
from the Data Display window, clicking on Tools > Search Table (Ctrl-S)

or clicking on the Table Search icon .

The Table Search function operates slightly differently in these three cases:

when invoked from the Database Explorer, AQT will display the table with the search condition applied to it.
when invoked from the Run SQL window, AQT will set the Run SQL text to the table-search SQL
when invoked from the Data Display window, AQT will apply the search condition to the displayed table. The Search Table function will remain open for further changes to the search conditions, until you close it with the Close button.

Table used in the Search Table window

When the Search Table window is opened, it will display all the columns in your table.

If you invoked the Search Table from the Run SQL window, the table shown in the Search Table window will be your query table (this is the table whose columns are displayed in the Run SQL window)
If you invoked the Search Table window from the Data Display window, the table shown will be table which is being displayed in the Data Display window. If the Data Display window is displaying a join, AQT will use the first of the join tables.

When you open the window

AQT remembers the search conditions you specified the last time you used the window. If the table name hasn't changed, these remembered search conditions will be filled out on the window.

When you invoke Search Table from the Data Display window, the cell you have selected in the Data Display window will be set up in Search Table as a search condition. You can use this feature to search for a particular column value with a few clicks.

How the Search works

AQT allows you to enter 5 searches. Each search will return a set of rows. If you specify two searches, you will see all the rows which meet Search1 plus all the rows which meet Search2 (from an SQL point of view, the searches are ORed).

Each search consists of a set of conditions on columns in the table. The search will only display rows which meet all the specified conditions. In the example at the top of this topic, the search will find all customers whose first name is 'CARL' and with a customer_type of 'R', plus the customers who have an 'S' in their last_name.

Number of Searches

By default you can enter 5 searches. You can change this to a different number with Options > General > Number of searches on Search Table window.

Column Search Condition

The column search conditions that can be specified are as follows

Condition	What it does	Example	What matches
text	column exactly matches text	com	com
='text'	This is an alternate way of specifing a column match. Useful if the text contains a comma.	='com'	com
text	column contains text	com	command, sitcom
text*	column starts with text	com*	com, command
>value	Column is greater than value. Other operators that can be specified are <, <= and >=. Value can be either a string value or a number.	>20 >y	21, 99, 4232 young, zoo
a to b	column is between a and b (this includes a and b). a and b can be string values or numbers.	1 to 7 j to q	1,2,3,4,5,6,7 jack, ken, peter
null	column is null
blank	column is blank. Same as =''
in (list of values)	column is one of the values	in (1,3,5)	1,3,5

Other things you can specify:

not	This selects all rows which do not meet the condition
date	Useful when comparing date values. date returns the current date
now	Useful when comparing timestamp values. Returns the current timestamp value

Multiple Column Search Conditions

You can specify multiple search condition by separating the conditions with a comma. The column meets these conditions if any of the conditions is met.

Example	What it does
ibm,ca	column is either equal to ibm or contains ca
=6,>20	column is equal to 6 or greater than 20
not 5 to 7	column is not between 5 and 7

Whether searches are case dependent

Whether the searches are case dependent depends on your database. Databases such as Oracle and DB2 are case dependent, whereas databases such as SQL Server, Sybase, Access are case independent.

Get Values

When you click on a cell, you get a Get Values button at the right end of the cell. Clicking on this will get the values of the column in the table. For a large table, this could take a while to process. The list of values appears in the grid on the right (as in the example at the top of this topic).

Clicking on a value will create a search condition of this value. You can select multiple values (using the Shift or Ctrl keys) - this will build an IN clause for the column.

The option Show "Get Values" buttons will control whether you see the Get Values buttons in the cells. If you de-select this, you can still get the values for a column by clicking on the Get Values button at the bottom of the window.

Show all rows

Clicking on this will show you all rows in the table (no search condition).

Clear Search

Clicking on this (or Ctrl-D) will clear the selected search. To clear all searches, select the menu item Clear > All Searches.