Viewing & Searching Data

Finding Data in a Table

Table of Content

Table of Content

Table of Content

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).

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.

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).

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.