Viewing & Searching Data
Finding Data in a Table
AQT provides two tools for find data in a table:
the Search Table window. This provides and easy way of building a complex search on a table.
the Find Data window. This allows you to search a number of tables for a piece of text.
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.
More Complex Searches
The Search Table function is designed as a simple window for building quick and simple searches. For building more complex searches you should use the Query Builder.
Find/ Replace Text in Table
The Find/Replace Text in Table window allows you to search a number of tables (for instance, all the tables in a schema) for a particular piece of text. You can also replace the text with another piece of text.
You activate this window from the Database Explorer with Table > Find Text in Table.

To search for some text:
enter the text you are searching for in the Search for field
select the tables you wish to search. You can use Select > Select All (or Ctrl+A) to select all tables. Filter can also be used to select a particular group of tables.
click on Search to run the search.
While running the search:
click on Abort to cancel the current search
click on Pause to terminate the search after searching the current table
The rows that are found are displayed in the grids at the bottom, with a separate tab/grid for each table. You can right-click a grid to see the options for that grid.
What Columns are Searched?
Find Text in Table 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.
This is discussed more in Advanced Search.
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.
Replacing Text
To Replace text:
select the Replace With option
enter the Replace Text
AQT will not immediately do a replace. Instead, it will generate an SQL script to do the replace. This gives you the opportunity to view the changes to your table before they are applied.
specify the file the Replace SQL Script will be written to
once the Find has been run, click on View to see this script, or Run to run it

Example of SQL Script
In the above example, the Replace SQL Script is as below:

Replace entire value with Replace text
This option governs what the replaced value will be.
By default (this option not checked), the new value will be the value with the Find Text replaced with the Replace Text, as per normal Find/Replace. In the above example, this would be jon24@aworks.com.
If this option is checked, the new value would simply be the Replace Text. In the above example, this would be aworks.com.
Do mass update if table has no unique key
The Replace function works best when your table has a unique key. The Replace Script will generate a number of statements, each of which will update a single row.
However, you may be updating a view, or a table which does not have a unique key. In this case, select the
Do mass update if table has no unique key option. The Replace Script will consist of statements such as:
Update dbo.DimCustomer Set EmailAddress = 'jon24@aworks.com' Where EmailAddress = 'jon24@adventure-works.com'
Each of these statements could potentially update more than one row. Use this option with caution.