Data Display Window

The same window displays either the contents of a table, or the result of a query.

Principal functions of the window are:

• Build a query

• Find rows

• Group columns

• Sort columns

• Compare contents of two tables, or contents of one table at different times

• Pivot report

• You can change the appearance of the display, or set some options.

Data Display toolbar

Many of these buttons will only be visible in certain circumstances:

• Collapse / Expand / Calc will only be visible when you have grouped your data

• More / Close will only be visible when your query is still active (see Get More Rows)

• Apply will only be visible if your table / query has a layout file than can be applied

You can customize the buttons that appear on this toolbar.

Some tasks available in the Data Display Window have shortcut keys.

Saving displayed data

You can save the data in this display (to disk) and retrieve it later. This is very useful if you are displaying some useful data that you may wish to refer to at some later time.

• Click on File > Save Results and specify the name of the file where you want the results saved. The query-result is saved in an AQT proprietary format. The file type will be grd (AQT saved-grid).

• You can use File > Open Saved Results to redisplay a saved results-grid.

• When you have saved the results of a query, this will be shown in the SQL History window - a table-icon will appear in the Saved column. You can re-display the results by clicking on this table-icon.

• The Layout of the display will be saved also (as a separate file).

Exporting Data

Use File > Export Data (or F7) to export your query results to a file. This will open the Export Data window where you can select the options for the export.

Updating, Deleting, Inserting Rows

To do this, click on Row > Update mode, Delete mode etc, or click on the appropriate icon. These will take you into Update Mode in the Row Display window.

You can also edit data directly in the Data Display window. Quickly make small amendments by using the right mouse menu.

Refreshing the Data

If you have run an update and changed the data, hit Refresh or F5 to redo the query and redisplay the data.

The sort order, row and column position, and grouping will be reestablished after the data has been refreshed. If you have done a Find (Ctrl+F) with the "Only show rows that match" option, the find will be reapplied. Other formatting of the data will be lost; if you have a saved-layout for your data, you can reapply it with View > Apply Layout.

You can also do a Full Refresh (Ctrl+F5) - this refreshes the data without restablishing sort order, row/column position or grouping.

Query Builder and SQL icons

To amend the query that is displaying the data, you can click on either:

• the SQL icon, which opens the Run SQL window, where you can alter the query

• the Query Builder icon, which allows you to edit the query in the Query Builder.

Get More Rows

You will see the Get More Rows button if you have enabled the Get More Rows feature (see Options > Run SQL) and haven't retrieved all the rows from the table/query. This button will get another set of rows from the table. This enables you to scroll through a large table, even if your Max Rows Displayed option is set to a low value.

When this button is visible, your query is still active and (depending on your database) will be consuming resources on the database. As soon as you have finished viewing the data you should close the query: use the Close Query button (or Ctrl+Z).

Note: Get More Rows will only scroll you forward in the database. There is no option to get the previous set of rows.

Close Query

This will close the active query. Once the query has been closed, either when you have clicked on this button or when the table/query has been read to the end, the Get More Rows and Close Query Cursor buttons will no longer be visible.3

Data Display options

There are a number of ways you can change the appearance of the display.

Grid Style

The general appearance of the grid (colors etc) is governed by the grid style. This is specified on Options > Grid Appearance > Style. AQT provides a number of pre-built style, or you can create your own customized style.

You can change the style used on a particular display window with View > Amend Layout (or the Layout toolbar button) and change the Style Sheet.

Font

By default, the Font used for the display is given by Options > Grid Appearance > Font.

If you wish to change the font used for this particular display, use View > Amend Layout.

Column Widths

Columns will expand to the width of the data, up to a maximum width. The maximum width is specified in Options > Grid Appearance > Maximum column width.

You can manually change the width of columns by dragging the column separator (in the column header).

Row Numbers

You can display a Row Number column by selecting View > Row Numbers (or F4), or clicking on the Numbers toolbar item.

You can specify whether row numbers are displayed by default with Options > Display Options > Show Row Numbers.

Grouping

You can specify whether a Group-by Box is displayed with View > Group-by box (or Ctrl+G), or clicking on the Group toolbar item. The group-by box allows you to group your data by particular columns. This is discussed in Analyse data by grouping columns.

You can specify whether the Group-by box is displayed by default with Options > Grid Appearance > Show group-by box.

Row Height

The height of rows is governed by Options > Grid Appearance > Minimum Row Height. You may wish to increase this if you are using a larger font than the default.

You can display the data with variable row heights; in this case the height of the rows expands to fit the size of the data. You select this with View > Variable Row Height (or Ctrl+H) or the Height toolbar button. You can specify that Variable Row Heights are used by default with Options > Grid Appearance > Variable Row Height.

Options > Grid Appearance > Maximum Row Height specifies the maximum height for the rows.

If you have particularly large column values, see the section Displaying Large Text Values to see how to display and view these.

Fixed-column Splitter

You can Fix columns so that they don't move when the grid is scrolled left / right. Once way of doing this is to have the Fixed-Column Splitter on the grid - this appears on the grid at the left of the column headers. The use of this is discussed in more detail in Using the Grid.

You can display the Fixed-Column Splitter with View > Show fixed-column splitter. You can specify whether this is displayed by default with Options > Grid Appearance > Show fixed-column splitter.

Column Titles

By default, the titles of the columns are the names of the columns in the query. For complex queries, it can be more useful to have the column expression used as the title instead. For example, if the query is:

Select EMPNO, SALARY+BONUS from TEST.EMPLOYEE

Then the column titles will be EMPNO and 2. When View > Use column expressions as headers is specified, the column titles will be EMPNO and SALARY+BONUS.

Amending the Layout of the grid

You can get more comprehensive options for the layout of your data by clicking on View > Amend Layout.

Saving the Layout of the grid

Once you have adjusted the layout of the data according to the way you wish to view it, you can save this layout information with View > Save Layout. This is described in more detail in Saving / retrieving the display Layout.

Using the Grid

The following function can be done with the grid.

Context Menus

The grid has a number of context (right-click) menus.

• right-clicking the grid will show you a number of functions that can be performed on the grid

• once you have selected a range of cells, you will get a slightly different menu with the functions relevant for a range of cells

• right-clicking a column header gives you the functions for manipulating columns

• right-clicking a row number (if you are displaying row numbers) gives you the functions for manipulating rows

Many of these functions are also available with the View and Grid menu items.

Note: when you right-click a row number, the options will apply to the current row (the one with the row pointer in the row number column), NOT the row the mouse was over when you did the right click. It is generally a good idea to left-click a row (to select it) before right-clicking it.

Sorting the data

• You can sort the contents of the grid by clicking on the column header.

• This sorts only the data that is in the grid. It does not sort the entire table. If you want to sort the table, include an ORDER BY clause in your SQL statement.

• As you click on the header the sort-order switched between Sort Ascending, Sort Descending and No Sort (so the grid returns to its original order).

• If you wish to sort by multiple columns, click on the Layout button to go to the Data Layout window. Select the Sort Order (Asc/Desc) for the columns

Selecting Data

You can select cells in a number of ways:

• click on a cell, hold down the Shift key, then click on a second cell. All cells between these two cells will be selected.

• use drag-select. Click on a cell than drag-over other cells; as you do so these cells will be selected. Drag-select can be enabled / disabled with Options > Display Options > Enable drag-select.

• to select an entire column, click on a cell, right-click and select Select Column

• to select an entire row, click on the row number on the left. Alternatively, click on a cell, right-click and select Select Row

• you can select all cells with Ctrl+A

As you select the cells, information about the range of cells you have selected will appear on the status bar at the bottom of the window.

If your selection contains any numeric they will be summed; the total will be displayed in the status bar at the bottom of the window.

Copying Data

To copy some cells (to the Windows clipboard), select the cells (as described in the previous section), then either hit Ctrl+C, click on Edit > Copy, or use the copy icon.

If you are copying more than one cell, you will be prompted for a number of copy options. These are discussed in more detail in Hints on Grid Usage.

Changing the Order of Columns

You can change the position of a column in the grid by clicking on the column header and dragging it to a new location.

Alternatively, click on the Layout button to go to the Data Layout window. You can use the Move buttons to change the order of the columns.

Hiding Columns

• You can hide columns from the display by right-clicking the grid and selecting Hide Column.

• Alternatively, you can click on the Layout button to go to the Data Layout window. You can hide / unhide columns with the Hidden checkbox.

• You can undo the last column-hide with View > Undo Last Hide Column (Ctrl+U). You can undo all column-hides with View > Undo All Hide Columns.

• You can automatically hide columns that contain values which are all null. This is done with View > Hide all Null columns (Ctrl+N).

• You can automatically hide columns that contain some other value with View > Hide all columns where...

  You can specify multiple values separated by a semicolon. So if you speficy a value of null;0;Y; then AQT will hide the column if all values are either Null, 0 or Y. Use <b> to specify the value being blank.

  The Hide all columns where value is saved between AQT sessions.

As you move or hide columns the underlying query is amended for the new column order. This therefore provides a very easy way to build a simple query.

Finding Columns

If you have a lot of columns in your grid, you can go to a particular column with Grid > Find Column (Ctrl + Shift +F).

AQT will move the grid to the first column that contains the string you specify. Ctrl + Shift + F3 will "re-find" - eg. move to the next column containing the search string.

Fixing Columns

You can "fix" a column - the column will be placed on the left side of the window and will not move when the grid is scrolled left / right.

You can fix a column in a number of ways:

• right-clicking the column and selecting Fix Column.

• if the Fixed-Column Splitter is displayed (see Display appearance) then you can fix columns quickly by dragging the splitter to the right (as shown below).

• dragging a column left into the fixed-column area

You can un-fix a column by:

• right-clicking the column and de-selecting Fix Column.

• dragging the Fixed-Column Splitter left to exclude the column

• dragging the column out of the fixed-column area.

If you can also Fix / UnFix columns by clicking on the Layout button and using the Fixed option for the column.

Hiding Rows

You can hide a row (or a range of rows) by:

• clicking a row (so it is the current row) or selecting a range of rows

• right-clicking the row number and selecting Hide Row

• alternatively you can click on Grid > Hide Row

Rows can also be hidden using the Find function with the "Only show rows that match" option. When this is done, all rows are hidden except for the rows that meet the specified condition.

You can re-display the rows by right-clicking and selecting UnHide All Rows, Grid > UnHide all Rows or Find > UnHide all Rows.

Highlighting Rows

You can highlight a row; when this is done the row background is changed to yellow (or other color as specified in Options > Grid Appearance). This makes it easy to read a row you are interested in. You can highlight any number of rows.

Highlighting of rows is done by:

• clicking a row (so it is the current row)

• right-clicking the row number and selecting Highlight Row

• alternatively you can use Grid > Highlight Current Row.

• you can unhighlight using similar menu options.

Displaying Large Text Values

If your table contains large text values, there are a couple of options that help you display these:

• the amount of data displayed for any value is determined by Options > Display Limits > Max Columns Size. By default this is 1000. If you have large text values you may wish to increase this to a larger value.

  If your data is large than 100K bytes, it is recommended to use the Display LOBs feature.

• select View > Variable Row Height, Ctrl+H or the Height icon. The height of the grid rows will expand to show the full text of the data.

• right-click a cell and select View. This will take you to AQT's Detail of Data Value window.

• you can display the cell contents using a particular application by right-clicking the cell and selecting View As. This is discussed in more detail in Displaying BLOBs and CLOBs.

Detail of a Data Value window

This window shows you the full value of a cell.

Note that if the data contains a hex0 character (x'00') the data will be truncated at that point. This is a limitation of the Windows text box used in this window. The way to avoid this problem, AQT will replace any hex0 characters with a ^ to enable the full value to be displayed.

Displaying XML Values

XML data is generally held as large text values, using the data types of CLOB, Long Varchar or XML.

To display the full value of these fields, you need to either have a high value of Max Column Size, or use the Write LOBs to Files option. This is discussed in more detail in Displaying Large Text Values.

We recommend the use of Write LOBs to files, as in the example below.

To see the full XML value, double-click the grid value, or right-click and select View.

The Detail window provides a number of different ways of viewing the XML value. These options are also available on the View menu.

These are described in the following sections.

Displaying Formatted XML

To option Formatted XML will format the XML into a more readable form by:

• adding line breaks and tabs

• highlighting keywords

Apart from these, the value is not changed in any way.

XML Viewer

AQT's XML viewer will parse the XML and display it in a tree format.

AQT will use internal Microsoft components for parsing the XML. If there are any errors in the XML, the parsing will fail.

Compact Mode

With a large XML file, the tree display can be overly verbose. To view the tree in a more compact way, select View > View XML in Compact mode.

View XML in Grid

This display is only useful for a particular category of XML values. These are single-level XML structures, such as the XML export of a database table or similar structure.

The following three screenshots show the same XML structure:

Or, to show this in a clearer way:

Clicking on View XML in Grid shows this XML structure as....

Displaying JSON Values

AQT is able to format JSON values in a more easily-read format.

• display the detail of a value, as explained in Displaying Large Text Values

• in the Detail window click on View > Format JSON or click on the JSON icon in the toolbar

Displaying Binary Values

To retrieve and display large binary columns (such as Blob and Image) we recommend you use the Write LOBs to Files option. This will write the binary values as separate file; you may then view these files or open them in an external application (such as an image editor).

For small binary columns, AQT has a number of options about how these are displayed.

Display binary data in Ascii mode

The data will be displayed as Ascii data. This may be unreadable.

If you data contains any x'00' characters, the display will be truncated at this point. AQT will circumvent this problem by substituting a ^ character for x'00'. You can use the Display x'00' characters as or Display unprintable characters as options to use a different character.

Display binary data in Hex mode

AQT will display the data in hex format.

You can choose to have AQT add a space between the hex values. This makes the values more readable.

Display data in Smart mode

AQT will parse the data and decide whether or not it consists of displayable characters, If so, it will display the data in Ascii mode otherwise Hex mode.

Using the Details of a Data Value Window

The Detail window is very useful for seeing the value of a binary field.

There are several tools on this window for examining the binary value.

Hex Viewer

Click on View > Hex Viewer, or click on the icon in the toolbar, to see a hex display of the data

View as Hex

For a simpler display than the above, click on View > View as Hex.

You can specify that a space is included between the bytes. This makes the display easier to read. This is specified in Options > Display Format > For Hex display add a space between bytes.

If AQT detects that you have a Unicode string, the hex string will consist of double-byte values. Note: if you aren't using the Add Space option it is difficult to know whether you have a Unicode or Ascii hex string.

Displaying BLOBs and CLOBs

Dealing with LOBs

BLOB and CLOB columns are difficult to display because:

• they are often very large, potentially several megabytes

• BLOBs often contain hex data, which is difficult to display meaningfully in the normal results grid

BLOBs and CLOBs are known generically as LOBs.

In addition, BLOBs and CLOBs often need to be sent to an external application to be displayed. For instance, an image file held in a BLOB column will need to be displayed using an image-viewer such as MS Paint. A Word document will need to be sent to MS Word.

Setting LOB options

To deal with these requirements, AQT has an option to export a LOB column to a file. Select this option by Options > Display Options > How to Display Lobs. Select Write LOBs to Files.

You can also select what column-types this is done for: BLOBs, CLOBs and LONG VARCHAR. To explain what these types mean:

• BLOBs means BLOB (DB2/Oracle), IMAGE and LONG VARBINARY (Sybase, SQL Server)

• CLOBs means CLOB (DB2)

• LONG VARCHAR means LONG VARCHAR (DB2), CLOB and LONG (Oracle), TEXT and NTEXT (Sybase, SQL Server) and MEMO (Access). It also includes any VARCHAR column that is longer than 5K bytes.

When you use this option:

• every LOB value will be written as a separate file

• when you display a table / result-set, the name of the LOB file will appear in the display

• when you double-click a LOB filename it will show you the Detail of a Data Value window. The first 1000 bytes of the LOB file will be shown. You can use Show As to display the LOB using a particular program.

• alternatively, from the results-grid, right-click a lob filename and use View As.

Storing and clearing LOBs

• the BLOB files are stored in directory lobs within the AQT default directory.

• the contents of the lobs directory is cleared out every time you switch on the Send to Files option.

• when you close a result-window, AQT will delete all the lob-files used in that display. If you wish to “permanently” save a lob-file, right-click it and select Copy LOB File to.

• If you wish to save the lob-files permanently, you can use the Export Data function. This can export LOBs to a directory which is not purged.

Problem when running multiple AQT Sessions

If you are running multiple AQT sessions, then it is possible for these sessions to interfere with each others LOB files. This is because both these sessions will use the same LOB directory and it could happen that they use the same LOB file names.

This issue is more of a problem in batch AQT as some functions (such as Data Compare) can retain the LOB files for the duration of the batch session.

To avoid such issues, you can manually set the directory used for LOBs. This is done with the following AQT scripting command:

--aqt options,lobdir="c:\aqt\workfiles\lobs"

Performance issues with LOBs

• the system will run slower with this option switched on. Not only are you creating a lot of files; but you will also be pulling a lot more data from your database server.

• if you return a large result-set, or have very large LOBs, then you can fill up your disk very fast. Use this option with a degree of caution.

Unicode

If you have enabled Unicode, AQT will write all CLOBs and Long Varchars values as unicode files. If you don't want your files in this format, you should disable Unicode (see Options > Unicode Options).

Limitations and issues with LOBs

The Row Detail window does not (yet) display LOB values. In a future release we may amend this to display the LOB files, plus edit and create LOBs (by linking to the appropriate external application).

Oracle ODBC driver needed

• if you are an Oracle user, you need to use the Oracle ODBC driver rather than the Microsoft ODBC Driver for Oracle (which does not support BLOBs).

Displaying LOBs with Sybase

In order to display Sybase LOBs, you need to have a high value set for your TEXTSIZE setting. This can be done with (for example):

set textsize 20971520

Alternatively, you can change the Default Buffer Size for Long Columns setting on the configuration of the ODBC Datasource.

Displaying EBCDIC Values

DB2 z/OS and DB2 iSeries database can hold data using EBCDIC encoding.

If the columns are defined as character, the data will come through to AQT in Ascii (so will be readable). Conversion from EBCDIC to Ascii is done by the gateway software - DB2/Connect or DB2 CAE.

However if the columns are defined as binary, then the gateway software will not do any codepage conversion - the data will come through unchanged. If these columns contain any text, this will come through to AQT in EBCDIC (which will not be readable).

Note that Binary columns include those defined as BINARY, VARBINARY or CHAR FOR BIT DATA.

AQT has a number of methods for dealing with this issue.

Options > Convert EBCDIC to Ascii

If Options > Display Format > Convert EBCDIC to Ascii is selected then AQT will parse all binary columns to see whether they contain EBCDIC text. If so, AQT will convert the text to Ascii.

This process is not perfect and AQT may not correctly identify a value as Ebcdic.

Detail Window

If your display has Ebcdic values, you can:

• show the value in the defail window

• click on View > Convert EBCDIC to Ascii

EBCDIC to Ascii Conversion Tool

AQT comes with a tool to convert EBCDIC values to Ascii. This is found in the Database Explorer window > Tools > Base64 / EBCDIC / Hex Encoding Tool. You can use this to convert any text, or file, between Ebcdic and Ascii.

Analyse data by grouping columns

When you are displaying your data in the Data Display window, you can use a function of the grid to automatically group your data by particular columns.

If you select View > Group-by Box (or Ctrl+G) AQT will display a Group-By box at the top of the display. This allows you to group your data by particular columns; this function is extremely useful for analysing your data.

• If you drag a column to the group-by box, the data will be grouped by that column. You can group-by multiple columns.

• You can expand or collapse sections of the group hierarchy by clicking on the + or – icons or double-clicking the item.

• View > Collapse Rows / Expand Rows will collapse or expand the group hierarchy.

• If you are grouping by multiple columns, Collapse/Expand will just collapse or expand the top column in the grouping. You can also use View > Collapse All or Expand All to collapse / expand all groups in the hierarchy.

• You can expand or collapse the grouping by double-clicking the column in the Group-By box. Note that if you expand a column, but one of the higher-level column is collapsed, you will not see the expanded data. The columns at the higher levels will also need to be expanded.

• You can removing the grouping by dragging the column and dropping it onto the grid (ignore the large X that you see when you do this).

Once you have grouped your data, you can calculate sub-totals on the data.

Using the Parameters Panel

If you are displaying a query which has parameters, the display window will show a panel with the parameters. This makes it easy to rerun the query with different parameter values. Select the values then click on Go.

The Go button will not be shown if you have only one parameter and it is a dropdown list with the Freetext option set the False. In this case the query is rerun as soon as you select an item from the list.

Searching for Data

Within the Data Display window, there are a number of ways you can search for particular data.

Finding Rows

You can do a "simple" find - this scans the grid for a particular string. You can do this by clicking on the Find button, or with Edit > Find (or Ctrl+F). There are three options for finding rows:

• Standard. This will scan forward until it finds the first cell that contains your search string. The cell will be highlighted. You can continue searching forward with F3.

• Hide the rows that do not match. Only the rows that contain the string are shown; the others will be hidden. In many cases this is the most useful option. You can specify two strings - the row has to contain both strings.

• Highlight the rows that match. This is similar to the previous option, except that all the rows are displayed; those that contain the search string are highlighted in yellow.

In the case of the second two options, you can re-show all rows with Ctrl+F > UnHide all rows. The row-hide / highlight will also be reset when you refresh the data.

Note that this function just scans the data that is displayed in the grid. If you aren't displaying all the table, there may be rows in the table that aren't found by this function.

Search Table

Another options is to use Search Table (see Tools > Search Table or the Search icon). This provides a simple dialog for building simple searches against your table.

Unlike the Find, this will run a query against the database so that all rows that meet the specified condition are returned.

If you wish to build more complex searches, click on the SQL or Query Builder icons.

Show/Hide Rows with Value

Another quick way to build a search is to right-click a value in the grid and select either Only show rows with this value or Exclude rows with this value. AQT will build a query to display or exclude those rows from the table.

You can select Only show rows with this value (another condition) to add this search condition to your existing search conditions.

If you want to re-show the entire table, right-click and select Display entire table.

Totals and Sub-totals

Totals

You can get totals of your columns by clicking on the Totals button, View > Totals or Ctrl+T.

You do not need to specify which columns are to be totalled - AQT will do this for all numeric columns in your data. This function is fast when your data has many numeric columns; the disadvantage is that it may total columns which are not meaningful to total (such as part numbers etc).

In addition:

• as you select a range of cells, if they contain any numeric values, the total will be displayed in the status-bar at the bottom of the window

Sub Totals

Once your data has been grouped (as described in Analyse data by grouping columns), you can display sub-totals and other calculations on the groups. You do this with (from the Data Display window) Tools > Group Calculations (Ctrl+K).

The Group Calculations window allows you to display a number of different statistics. These will appear in either the Group Header or Group Footer of the display grouping.

The values appearing in the Group Header will be visible when the display grouping has been collapsed - it can be useful to have some statistics displayed in this.

The Group Calculations window shows you all the columns in the table:

• check a statistic (Min, Max, Count) if you want that statistics calculated.

• for numeric columns, the Sum and Avg boxes will also be enabled.

• by default, the statistic will be shown in the Group Footer. Check the Show in Header? box if you want the statistic shown in the Group Header.

• as you select statistics, sample text will be shown in the Header and Footer boxes. You can amend this text if required.

Once you have created some group calculations, and have set up the data display in the way you want, you can save the layout with View > Save Layout. When you save the layout, this includes grouping and group calculations. This layout can be re-applied to the table at a later time. For more on this, see Saving / retrieving the display Layout.

Use Correct Data Types for Grid Columns

In order for both these functions to work, you need to set Options > Display Options > Use Correct Data Types for Grid Columns.

When this option is set, numeric columns are defined to the grid as being numeric. When the option is not set, all grid-columns are defined as character. Totalling and sub-totalling only works when the grid-columns are numeric.

How Numeric Values are Displayed

Warning: this is a technical topic for advanced users only.

We are often asked how numeric (in particular decimal) values are displayed in AQT, and how to change the way they are displayed. This is a surprisingly complicated area - we describe here how it works.

The are two parts to this discussion:

• how numeric values are retrieved from the database by AQT

• how numeric are displayed in the grid

How Numeric Values are retrieved from the Database

When data is retrieved from the database into AQT, AQT supplies program variables for the data to be placed into (by the ODBC Driver). For numeric variables, AQT has a choice of retrieving them into either:

• numeric program variables (in which case they "go into" AQT as numbers)

• string program variables (in which case they "go into" AQT as strings)

Most numeric values go into AQT as numbers. Except:

• decimal column types. These always go into AQT as strings. This is because PCs do not have a numeric data type that corresponds to a decimal column type. The only way to display the decimal value accurately is to retrieve them as strings.

• if Options > Technical Parameters > Bind all cols to strings is specified. In this case all columns go into AQT as strings.

When AQT retrieves the decimal value as a string, it is the ODBC Driver that decides how the decimal value is to be formatted as a string. So, it is the ODBC Driver that decides whether to display the decimal value as (for instance) 12.345 or +1.2345E+1.

Some ODBC Drivers have options that specify how it is to format the decimal values. For instance SQL Server has the setting Use regional settings when outputing currency, number, dates and times. When this option is selected, SQL Server will use the Control Panel regional settings for formatting the decimal values.

How Numeric Values are displayed in the Grid

Once AQT has retrieved the numeric value, it will place the value in the display grid. There are two ways in which the grid works. This is governed by Options > Display Options > Use Correct Data Types for Grid Columns.

• when this option is not selected, the grid columns will be defined as string columns. The numeric values will be displayed as AQT has retrieved them from the database.

• when this option is selected, the grid columns will be defined as numeric columns. The grid will then choose how it will display the numeric value. By default, this will be as per Control Panel > Regional Settings. This may be different to the way AQT has retrieved the values from the database.

  You can change the way numeric values are displayed in the grid with View > Format Data (from the Data Display window).

Exporting Data

When data is exported, numeric values are written as they have been retrieved from the database.

Decimal Separator is Comma

Databases always return decimal values with a dot as the decimal separator, even in cases where this has been set as a comma in the Regional Settings.

AQT will detect that the Regional Settings have specified a comma, and will replace the dot with a comma in the decimal values that are retrieved from the database.

If you have specified Use Correct Data Types for Grid Columns, the grid will also be aware that the decimal separator is a comma. There has been the odd problem whereby AQT hasn't replaced the dot with a comma when it should have. When this happens, the grid interprets the dot as a grouping-indicator (thousands separator) and has displayed the decimal value as 1000 times the correct value. If you suspect this might be happening to you, switch off Use Correct Data Types for Grid Columns.

Formatting Data

For more comprehensive data formatting options, from the Data Display window, click on View > Amend Layout or click on the Layout button. This will take you to the Data Layout window. Within this window you can:

• set the caption for the display grid

• change the order of the columns in the display, by clicking on Move Up, Move Down or Move To...

• display the columns in alphabetic order. To do this, click on the Column header to sort by the column name. This will cycle between sort ascending, sort descending and no sort.

• specify the alignment of the data in the columns.

• specify a customized Format for the column (this is described below)

• specify whether columns are hidden by checking the Hidden checkbox

• specify whether columns are fixed by checking the Fixed checkbox

• adjust the Column widths

• specify the display sort order.

Saving / Loading the Display Formatting

Once you have formatted the data in the way you want, you can save the data layout. See Saving / retrieving the display Layout for more on this.

Sort Order

Use this column to specify the columns the display is to be sorted by.

Sorting by multiple columns

You can sort by multiple columns.

When you are sorting by multiple columns, the sequence that they will be sorted by is the order the columns appear in the display. For instance, if you wish to sort by ColumnA, then by ColumnB, you need to have ColumnA appearing before ColumnB in the display.

Format

This specifies the data format AQT uses to display the data in the grid. Note that this determines how the values are displayed - the underlying data value is unchanged.

For character columns, AQT uses a format of General.

For numeric columns, by default AQT will use a format of General. You have the choice of a number of a number of other options:

For date and time columns, by default the values are displayed as per Options > Display Format > Format of Date Time columns. The following other formats are available:

Custom Formats

For numeric values and date / time values, you can also use custom-formats, for example

• $###,##0.00

• dddd MMMM yyyy

• (000)000-000

In these:

Saving / retrieving the display Layout

Once you have adjusted the layout of the data display according to the way you wish to view it, you can save this layout information with View > Save Layout. The layout information includes:

• display caption

• column order

• column alignment

• column formatting

• column sorting

• whether columns are hidden

• whether columns are locked

• column widths

• column grouping

• column sub-totals

• other attributes of the grid

• cell highlighting

Some (though not all) of these attributes can be specified in Formatting Data.

Default Layout File

The default name of the layout file is:

• (for saved-queries) layout_queryname.txt in the directory where the query is located

• (when displaying a table) layout_tablename.txt in the Location of saved queries directory.

It is generally recommended that you use the Default Layout name when saving the layout.

If Options > File Locations > Location of Layout Files is specified, the layouts will always be held in that directory.

Applying a Layout

When you display a table or query, AQT will see whether the Default Layout File exists for the table / query. If so the Layout button will displayed in the toolbar. Clicking on this will apply the layout to the current display. You can also do this with View > Apply Layout (Shift+F3).

If you wish to apply another layout file to the display, go View > Load Layout. You will be prompted for the layout file to use. Note that this window will only show you layouts named layout_*.txt, so if you have given your layout a name not in this format you will not see it in this list.

Refreshing Data

If you refresh your data in the Data Display window, it will retain some, but not all of the current formatting. Click on View > Apply Layout to reapply the layout.

Examples

To see some examples of display layout, see the demo database aqtdemo. Display the customer_details or order_line tables then click on the Apply button.

Automatically Applying a Layout

If you want the layout to be applied automatically, you can do this in a number of ways:

• before running a query, specify the layout file to be used with an --aqt layout command, Eg:

  --aqt layout,file=layout_emp.txt

  select * from Hr.Employees

  If this is an unqualified file name, it is taken to be in the Location of saved queries directory.

• to automatically apply a layout when you run a saved-query, specify Options > Display Options > Automatically apply Layout to Queries. When you run a saved-query, AQT will check whether the Default Layout file for that query exists. If so, AQT will apply that layout to the query data.

• to automatically apply a layout when you display the table, specify Options > Display Options > Automatically apply Layout to Tables. When you display a table, AQT will check whether the Default Layout file for that table exists. If so, AQT will apply that layout to the table data.

Highlighting Particular Cells in the grid

You can specify that particular cells in the grid are to be highlighted.

This is done with the Highlight Cells window. This can be invoked by View > Highlight Cells or click on the Layout button and select Highlight Cells.

On the Highlight Cell window you specify a series of conditions and, when matched, the highlight color of the cell.

• in Column, select a column name from the dropdown list

• in Operator, select a comparison operator from the dropdown list

• in Value, enter the comparison value. Note that this is case independent.

• in Color, left-click to specify the nackground-color of the cell. Right-click to select the foreground color.

If you select Only show rows that meet a condition, only the rows that meet one of the conditions will be shown in the grid. The other rows will be hidden.

Click on Apply to apply the hightlight conditions to the grid, and keep the Highlight Cells window open.

Click on Close to apply the hightlight conditions to the grid, and close the Highlight Cells window.

Highlighting is Saved with the Layout

When you save the layout of the table, the Highlight Cell conditions are saved with the layout.

When you apply the layout, the Highlight Conditions are reapplied to the display.

Multiple Data Display windows

You can have multiple Data Display windows open at the same time.

• By default, every time you run a query, AQT will display the table/query results in a new Data Display window

• Alternatively, you can have AQT re-use the Data Display window for subsequent queries. In this mode, AQT will have a single Data Display window, which it will use for all table/query displays.

• This behavior is controlled by Options > Window Behavior > Always open a new Data window. When this option is set, AQT will use a new Data Display window every time you run a query. If this option is not set, AQT will operate in re-use mode.

• When you are in re-use mode, clicking on the lock icon (or selecting File > Lock Window) will prevent that particular window from being re-used. The contents of the window will be retained until you either close the window or click on the lock icon to unlock it.

When you use Find Rows or Refresh Data, AQT will always re-use the current Data Display window, irrespective of the above settings.

Link to Related Tables

Once you are displaying a table, you can display rows in other related tables. This is shown as follows:

Related Tables

In order to use this feature, your table must have a number of relationships to other tables. There are two ways a table relationship can be established:

• it can be defined to the database as a Foreign Key relationship.

  • You can see existing Foreign Key relationships by, from the Database Explorer, right-click a table then selecting Dependent Tables or Related Tables. Some databases (such as MS Access and MySQL V4) do not support Foreign Keys.

  • You can define a new Foreign Key by, from the Database Explorer, select a table, then clicking on Create > Foreign Key in the Admin Toolbar.

• it can be defined to AQT as a User-Defined Relationship. This feature of AQT allows you to define relationships between tables, when they are not defined to the database. Click on Link > Edit List of Related tables to edit the list of User-Defined Relationships.

Showing Related Tables

To show the data from related tables, click on the Link toolbar button, Link > Link to Related Tables menu item, or hit Ctrl+F2. AQT will show the Related Tables panel at the bottom of the window:

• the related tables will be shown in the drop-down box. Select the table relationship you wish to view.

• alternatively, you can click on the button with three dots. This will show you all the related tables, and the details of the relationships. If your table has many relationships, this is useful for selecting the relationship you wish to view. Click on a relationship then click on the OK button. You can also double-click a relationship to select it.

• as you select a relationship, the relationship description is given to the right of the drop-down box. For Foreign Key relationships, this description is the Constraint Name. For User-Defined Relationships, this description is as given when the relationship is defined.

• as you click on rows in the main grid, the matching rows from the related table are shown in the lower grid. The SQL that AQT has used to get these rows is shown in the bottom status-bar.

• AQT doesn't provide many options for "doing" things with the related rows (eg. copying, showing row detail etc). If you wish to deal with this data further, click on Copy to another window. This copies the rows to their own Data Display window - from this you can copy / export / update the rows, or link to another table.

• The New option specifies whether a new window is created every time you click on Copy to another window. By default it isn't - a new window is created once (when you first click on Copy to another window) - when you click on Copy to another window a second time, the existing related-table window is re-used.

Building a Query Using the Data Display window

You can use the Data Display window to build a simple query.

• Move or hide columns to specify the columns to be in your query.

• Use the Find Rows feature to specify a search condition for your query.

As you use these options, AQT will amend the SQL that is behind this display. This SQL is shown in the Status Bar at the bottom of the window. If you click on the SQL or Query Builder icons, you will be taken to the Run SQL or Query Builder windows where you can amend this query further, or save it.

Pivot Columns

From the Data Display window, clicking on Tools > Pivot will create a tabulation from your results. You can use this to:

• display your data in a way that is easier to read

• summarise your data

What distinguishes Pivot from other forms of data manipulation / display, is that Pivot will create columns in your Pivot table from the values of one of your columns (the pivot column). This is something that is difficult to do with normal SQL.

The following shows an example:

Pivot Column

You need to specify which column is to be your Pivot Column. You can only have a single Pivot Column. The values of the Pivot column in your results become columns in the Pivot table.

Your Pivot column should be one which takes only a small number of distinct values. The Pivot table has a limit of 2000 columns so you should choose a column with a smaller number of distinct values this.

Grouping Columns

There will be one line in your pivot table per unique value of the grouping columns. You can have multiple grouping columns.

Analysis Column

This column contains a numeric value that is to be analysed. The values are placed in the analysis grid. If you have multiple rows in your results for each combination of Pivot and Grouping Columns, the values of the analysis column will be summed.

The analysis column is optional. There will be no analysis column if you click on No Analysis Column, or your results have no numeric column. In this case, the grid will be populated with the number of occurrences of the Pivot Column / Grouping Column(s) combination. The example at the end of this topic shows this option being used.

AQT will calculate row and column totals in the grid.

Sort Pivot columns?

When this option is selected, AQT will display the Pivot columns in alphabetic order (or, if the Pivot column is numeric, in numeric order). When this is not selected, AQT will show the pivot columns in the order in which is finds the values (this may seem to be a "random" order).

Show Sample

When you click on this, AQT will show you a sample of your pivot table. It will do this by reading a subset (first 1000 rows) of your data. This is useful for seeing what the Pivot table will look like. Note that it will not display the full amount of data or the full number of Pivot columns.

Saving / Loading a Pivot Spec

This can be done with Save/Load Pivot options. This if useful if you are running a certain Pivot on a regular basis.

Generate SQL

When this button is clicked, AQT will generate an SQL statement that will create a result-set similar to the Pivot table. Note that AQT itself doesn't use this SQL statement - it creates the Pivot results by processing the data in the grid.

• the SQL statement will not generate the Total row and column.

• the Pivot columns in the SQL statement will based on the values of the Pivot column currently displayed in the grid. If the contents of the table changes, you may need to re-generate the Pivot SQL statement.

• the SQL statement has a column Other, which contains values of the pivot column which aren't present in the Pivot columns. This is useful if your data changes after you have generated the SQL statement. Once you start seeing data appearing in the Other column, it is time to regenerate the Pivot SQL.

• this SQL statement may not necessarily run on all databases. The SQL uses the CASE statement which isn't support on all databases.

Other notes

• when you run Pivot, AQT will sort your source grid into the grouping-columns order. This is necessary for the way AQT processes the data in the grid.

• Pivot cannot be run in batch. However, Generate SQL provides you with an query that produces the pivot data. This query can be used in a batch script.

Another example of Pivot

In this example, a large table is summarized as a Pivot table:

• the Grouping Column is OWNER

• the Pivot Column is TABLESPACE_NAME

• there is no Analysis column. The cells in the Pivot tables give the number of rows with the given OWNER / TABLESPACE_NAME combination. For instance, the first line shows that there are 3 rows with an OWNER of 'COMP' and TABLESPACE_NAME of 'Example'

Use Slots

These options appear when your pivot column is a numeric or date column. In this case, you may not want columns in your Pivot table for every single value of the pivot column. Instead, you may want to specify a range of values to use. The following shows an example of this.

This example summarises the Order_Details table. It shows, for each part, how many orders are for 0-5 parts, how many are for 6-10 parts etc.

Note that:

• when a column has (for instance) >5 to 10, this means greater than 5, and less than or equal to 10.

• if you have selected the checkbox include end slot, AQT will add a column for values greater than the last slot you have specified (this is the >40 column in the example below).

• if your Pivot column contains null values, you may wish to check the include NULL slot checkbox. AQT will include a slot which contains the results for Null values of the pivot column.

• you can use this feature with numeric and date columns only. It should not be used with Timestamp or Time columns.

Tabulation

In the above examples, Pivot sums a numeric column and places the total in the pivot grid.

Tabulation, by contrast, produces a two-dimensional tabulation of your grid. No summarization takes place.

In the example below:

• EMPNO is the Grouping column

• PROPERTY is the Pivot column

• VALUE is the Analysis column

• the Tabulation option is checked

Some notes:

• Tabulation is used in situations where the combination of the Grouping columns plus Pivot column is unique. In the above example, EMPNO and PROPERTY are the unique key for the table; so for each combination of these there is only one value of the VALUE column.

  If Grouping plus Pivot are not unique, only one of the values of Analysis column will be shown in the grid. This may not produce meaningful results.

• When Tabulation is used, the Analysis column can be of any type (eg. can be character). For non-tabulation pivots, the Analysis column must be numeric.

• When Tabulation is used, there are no Totals row and column.

Comparing Table Data

AQT provides a basic mechanism for comparing the results of two queries. This can be used to:

• compare the contents of two tables. This works even if the tables are in different databases

• compare the contents of one table at two different points of time. This is useful for

  • time-varying data

  • checking the result of a large update

  • comparing the results of test scripts.

There are two ways you can use this feature:

Use the Compare to Another Results Window

This is the best method to compare two tables:

• display the first table or query

• without closing this window, display the second table or query.

• if you find that you can only display one table at once, click on the top-left icon to “lock” the Data Display, or use Options > Window Behaviour > Always open a new Data window.

• while displaying one of the tables, click on Compare > To Another Results Window. You will be shown a list of all the other Result Windows. Select the appropriate window.

• The data in the two grids will be compared, and you will be shown the Compare Results window with the results of the compare. This window is described later in this topic.

• If you want to exclude some columns from the compare, you can do so with options in the Compare Results window.

Use the Compare to Saved Results

This is the best method of comparing a table at two different points in time.

• display the table or query

• click on File > Save Results to save the data to disk. This data can then be recalled or compared-to at any time in the future.

• (later) redisplay the table or query.

• click on Compare > To Saved Results. Select the file where the results of the earlier query were saved.

• The data will be compared, and you will be shown the Compare Results window with the results of the compare.

Warnings

For the compares to work correctly you should always display the data in the same order. It is recommended that you have an Order By primary-key-columns in your Data Displays.

The compare is a “simple” compare; it is not smart enough to cope with rows being inserted or deleted. If this has happened, it will flag differences in all subsequent rows.

Make sure you are displaying all the rows in your tables by setting Options > Display Limits. Also make sure that Max Column Size is large enough for your widest column.

This feature cannot compare BLOBs, large binary fields, or columns larger than 10,000 bytes.

Save Results will save a maximum of 10,000 bytes per row. If you have some very large columns in your display, they may not be saved / compared correctly.

Compare Results window

This window shows the results of your data compare.

• the boxes at the top of the Compare Results window give you details of the queries you are comparing.

• Compare Cols allows you to select the columns you are comparing. By default, AQT will compare all columns in your data. You can redo the compare excluding particular columns from the compare. Click on the >>> to expand the column-list then select or de-select columns as appropriate. Click on Do Compare to rerun the Compare.

• the grid will have one line per difference found in the data. As you select an entry (either by clicking on it, or by using the up/down keys) AQT will tell you at the bottom of the screen what the difference is between the values.

• as you select entries, the related values in the table-display windows are highlighted. This makes it easy to see, in the table-display windows, the values that are different.

• you can double-click a cell value to see the full value. This is useful if you have very large values.

Row display and row change

Open the Row Display window from the Data Display window by any of the following methods:

• Use the Row menu

• Any of the five yellow icons (Row detail, update row, delete row, insert duplicate row, insert new row)

• Click in a row then right-click to display the quick menu.

In addition, you can activate this window from the Database Explorer window, by right-clicking a table and selecting Add New Row. This allows you to insert a row into a table without first having to display the table.

The Row Display window has two uses:

• to display the details of a table/query row

• to provide an easy way to update your data.

Toolbar

Some of these buttons will not be visible or active, depending on what mode (display, update etc) you are in.

You can customize the appearance and the buttons that appear on this toolbar.

Displaying rows

The Row Display window opens with the current table/query row displayed, or the first row if no rows were selected.

• use the arrow icons or the Goto menu-item to move to other rows in your table.

• for a particularly large value, click on the row then hit F2 or Edit > Extended Edit to see the data in a larger window.

• click on Print for the two print options: Print Screen and Print Data.

• resize the rows to display large column values.

Changing rows

Most of the other features of this window are to do with updating / deleting / inserting rows in your table.

To amend your table data, select one of the “update modes”:

• Update mode to update this or other rows in the table.

• Delete mode to delete this or other rows in the table.

• Insert Dup mode to insert a new row in the table, using the values of the current row as a base.

• Insert New mode to insert a new row in the table with the table columns initialised to default values.

  These will be the default values of the table columns as defined to the database. If these aren’t available, the default values will be blank, zero or current date/time depending on the data type.

• Preview SQL displays the SQL that is being generated. You will see the effect of any Where and Update options that have been set.

Updating a single table row

• Tick cell/s in the Where column to indicate the table columns that comprise the unique key. If your table has a primary key or unique key, these columns will be marked for you automatically.

  For Oracle and Informix users, if the ROWID is in the result-set, it will be used as the Where column.

• Change the data values. As you change a value, the Upd column will be ticked.

• Click on the Update button.

Get the existing values of a Column

• you can click on Get Values to get the existing values of a column. These will display as a dropdown list of values to select from.

• by default, Get Values will get the existing values of the column in the table. However if Options > Row Display/Update > Get Values from Foreign Keys is specified, AQT will see whether there is a foreign key defined for that column. If so, it will get the values of the foreign key column.

• clicking on Get Ext Values will show you the Extended Get Values dialog. This provides more options for the Get Values function.

• clicking on Get Constants will show you common constant values and expressions for the data type.

• once you have displayed a drop-down list using one of these buttons, it will be redisplayed when you next click on the column.

• for Date / Time and Timestamp columns, there will also be a button Select Date. This allows you to select a date/time value from a calendar box.

Updating multiple rows (mass update)

• Tick cell/s in the Where column to indicate which rows in your table are to be updated

• Change the data values. As you change a value, the Upd column will be ticked.

• Optionally, click on the Check Number button. AQT will tell you how many rows will be updated by your update. This is a useful safeguard before running a complex or critical update.

• The Preview button will display the SQL that AQT will run to perform your update.

• Click on the Update button.

• If you have Safe Update Mode selected, and you have updated more than one row, you will be prompted for whether this update is to proceed.

Note:

Delete mode

In Delete mode, one extra column, Where, will be displayed in the grid. Tick cells to specify which of the table columns form the Where clause for the delete.

Use the Preview SQL and Check Number buttons as described in the previous paragraph to check the effect of your proposed deletions before you run the delete by using the Delete button. Check Number is a useful safeguard before running an update that is complex or critical.

Updating / Deleting every row

When in Update or Delete mode, AQT will insist that you have a Where column selected. This is to prevent inadvertent updating / deleting of the entire table.

If you really wish to update / delete every row in the table, this can be done by:

• selecting some Where column (to keep AQT happy)

• click on Preview SQL

• remove the Where clause from the SQL

• click on Run

Insert mode

In this mode, one extra column, Include, will be displayed in the grid. Use this column to specify which table columns are to be included in the generated Insert statement. By default all table columns are included, however you might wish to exclude some table columns from this grid if you want them to pick up their default or generated value.

You can include all or no columns in the Insert with Edit > Include All or Include None.

Field view options

For large text fields, F2 or Extended Edit will display the entire value.

View a drop-down list of all existing values of the field by hitting F3 or Get Values. A drop-down arrow will appear on the value-cell. Click on this to get the list of values.

Ext Get Vals will show you the Extended Get Values dialog; this is similar to Get Values but gives you more options about the values retrieved.

F4 or Get Constants will give you a drop-down list of typical constants and literals for the column.

Altered data

If you have changed your column values but want to return to their original values, hit Ctrl+R or click on Row > Reload Current.

After you have updated / deleted / inserted some data, these changes will not be reflected in the Data Display. Refresh reruns the query to repopulate the data in the window.

Copy / Paste

You can copy multiple column values by selecting them and hitting Ctrl+C (you select multiple values by dragging the mouse over them).

You can paste data by clicking on a cell and hitting Ctrl+V.

• if you are edit-mode on the cell (row-pointer on the left is a little pencil), the data will be pasted into the cell.

• if you are not in edit mode, the data will be copied into either a single or multiple cells. AQT will examine the clipboard to see whether it holds a single value or multiple values. Multiple values are delimited by either a linefeed or a tab. This means that, when copying data from Excel or AQT, you can copy a set of values either horizontally (in a row) or vertically (in a column), and it will paste successfully into AQT.

Auto-generated data

If your table has an auto-generated column then (during Insert mode), AQT will place (auto) in the column. You should not supply data for this column as it will be generated by the database.

Key columns

If your table has a primary key or unique key, these columns will automatically be set as the Where columns during Update and Delete modes.

You can see which are your key columns by clicking on Edit > Show Primary Key (or Ctrl+K). The names of your primary key columns will be shown in blue.

If your table/view doesn't have a key, clicking on Edit > Show Primary Key will allow you define a User-Defined Key.

Internal RowID

For Oracle, Informix and SAP-DB, there is an internal Rowid that can be displayed with the table. You can display this column automatically by selecting option Options > Display Options > Show Rowid. If this RowID is being displayed, AQT will use it as the Where column for Update/Delete modes. This is useful if your table does not have a unique key.

Date, Time and Timestamp columns

• When you go into one of the update modes, any date / time / timestamp columns will be changed from their display-format to their update-format. If your timestamp column has a zero time-part (which is normally suppressed during the display of the column), you will notice that this has been added to the column. This allows you to update this time part of the column.

• For Oracle, you will get an option Add To_Date. This specifies whether you want the TO_DATE function applied to any date values being updated, or the TO_TIMESTAMP function for timestamp values. Click on Show Quotes to see the column value with this function included.

• You will get a button Select Date. Clicking on this shows you a date-picker window, which allows you to select a date, timestamp or time value.

LOB columns

AQT makes it easy to load a value of a BLOB/CLOB column from a file. Once you set focus on a LOB column a button Load from File will be displayed. Clicking on this allows you to specify a file to be loaded into the LOB during the Update/Insert.

• Once you have selected a file, you will get <aqtfile>filename in the column value. This is a control keyword to tell AQT to load the column from a file. You can also invoke this function by manually typing <aqtfile>filename (specify a valid filename) into this column.

• Click on Preview SQL to see how AQT processes the LOB insert/update. It will replace the <aqtfile>filename with a parameter marker (?), and use an AQT control statement to set the value of this parameter. It is done this way because files can only be loaded into LOB values by using parameters.

Special Values

• If you specify a value of ?, AQT will interpret this as a parameter marker and prompt you for the value when you do the update/insert. If you really want a value of ? inserted into your database, use the Show Quotes option to show how AQT will use quotes, then add your own quotes to get the result you want.

• If you specify a value of NULL, AQT will set the column to NULL. If you really want a value of NULL inserted into your database, use the Show Quotes option.

Using values with special meanings

By default, AQT encloses all string values in quotes, except

• Null

• ?

• Date/time columns with a non-numeric value

This works well in almost all situations, however you may need to be able to specify whether a string is enclosed in quotes.

Examples of this are:

• if you want to set your column to a system variable. Examples: SYSDATE, USER.

• if you set the column to another column, or a function involving another column.

  Examples: Current_Salary/100, Substr(Last_Name,1,1), Units_Ordered – Units_Delivered.

• you want to specify a value of ? or NULL.

• You want to include trailing blanks with your character value (normally AQT will remove trailing blanks).

To see whether AQT has enclosed a value in quotes, use the Show Quotes option to view the field as formatted. You can then add or remove quotes, as needed.

For example, without Show Quotes set:

Running a Change

• to run your change, click on the Update / Delete / Insert button.

• if you change more than one row, and you have Safe update mode selected, you will be prompted as to whether you want the change to proceed or not. This allows you to back out the change should it affect more rows than you expected.

• alternatively, before running your change you can click on Transaction to enter transaction mode. This will enable you back-out your change if you decide that it didn’t do what you want.

Preview SQL

Before running your change you can click on Preview SQL to see the SQL statement that will be run. While in the Preview window:

• you can modify the SQL

• click on Run to run the SQL.

• click on Copy to SQL Window to copy this SQL to the Run SQL window.

• click on Save Query to save the SQL. Retrieve Query retrieves a saved query.

• if you change this SQL but wish to return to its original version, click on Refresh SQL.

Check number of rows that will be affected

Before running an Update or Delete you can click on the Check Number button. This will run a query against the database to tell you the number of rows which will be affected by your Update/Delete. This is a useful safeguard before running a complex or critical update.

If you have a large table, this option could take a few seconds.

Sorting the Grid

By default, the grid is not sortable. If you wish to do this, click on Edit > Enable sorting. Once this option has been checked, the columns in the grid can be sorted by clicking on the column headers.

Deleting Duplicate Rows

If your table doesn't have a unique index, it is possible to end up with two (or more) identical rows in the table. Deleting one of the duplicate rows can be difficult to do using SQL, as any Where condition will result in both rows being deleted.

If your database allows you to display internal rowids, then you can use these to delete one of the duplicate rows.

Otherwise, the following procedure can be used:

• display one of the duplicate rows

• click on the Del button in the toolbar to go into Delete mode.

• Click on Delete. You will probably get a message saying You have affected 2 rows. Do you want this to proceed?. Click on Yes. Both the duplicate rows will then be deleted.

• now click on the Dup button in the toolbar to go into Load Duplicate Row mode.

• click on Insert. This will insert the row back into the table.