Options - Display Options

These are a number of miscellaneous options controlling how data is displayed.

Use Correct Data Types for Grid Columns

This is a technical option concerning the way AQT uses the grid.

When this option is selected, the columns in the grid will be defined as per the data type of the data being displayed:

• numeric columns will be displayed in a numeric grid column.
• date and time columns will be displayed in a date grid column.
• datetime and timestamp columns will be displayed in a character grid column.
• boolean columns will be displayed in a boolean grid column. They will appear as a checkbox.
• all other data types will be displayed in a character grid column.

In most cases, this will display the data correctly. However there are some possible problems:

• the data will not display as it was retrieved from the database. Instead, the grid will format the data using it's own formatting process. In some circumstances, you may wish to see the data exactly as retrieved from the database. This is discussed more at How Numeric Values are Displayed.
• if there is any "invalid" data, the values will not display; instead a flashing error-box will be shown. This can sometimes happen with date values:
  • any date less than 0001-01-02 or greater than 9999-12-31 will not display. This is a limitation of the .Net environment.; dates outside of this range are not valid dates.
  • DB2 displays midnight as 24:00:00. The grid will display this as an invalid time.

When this option is not selected, all grid-columns will be defined as character. The data will display more accurately, however:

• numeric and date columns will not sort correctly. They will sort in character order, rather than numeric or date order.
• the Totals and Sub-totals functions of the Data Display window will not be available.
• Pivot will not recognize any columns as being numeric; consequently it will think that there are no columns which are candidates for the Analysis column.

By default this option is selected. If you have a problem with the display of numeric columns, you should switch this option off.

Display Date columns as character

This is similar to the previous option, but this is only done for Date and Time columns.

This allows you display Date and Time columns as character, but display columns of other types normally. You would do this by selecting Use Correct Data Types for Grid Columns and also selecting this option.

Note: this option only applies to columns defined as Date or Time. Many databases do not have a Date and Time data type but store date and time values in a datetime or timestamp data type. This option does not apply to these, as these datatypes will be displayed by AQT in a character grid column.

Display Numeric Columns as Double

Normally, numeric columns are displayed in a grid column defined as Decimal. In some circumstances this cannot display particularly large values. To deal with this, this option can be selected; in this case numeric columns will be displayed in a grid column defined as Double. This can handle virtually all numeric values, however there can be loss of some precision digits for large values.

Show ROWID

This option is useful for databases that have an internal table row identifier that can be displayed with the contents of the table.

• for Oracle, Informix and SQLBase this is called the ROWID
• for SAP DB and MAXDB this is called the SYSKEY
• for PostgreSQL this is called the OID
• for DB2 for LUW, this is called the RID

When this option is selected, this rowid is displayed when a table is displayed. AQT does this by changing the table-display SQL to include the rowid column.

You may notice that this option doesn't come into effect right away - only on the next table you display.

Displaying the rowid is often useful when you wish to edit the data in a table. AQT will recognise the rowid column as a valid table key, and will use this to identify the rows you are updating or deleting.

Show Length of Varchar Columns?

This is a potentially useful option for database developers. When a value is inserted into a Varchar column the length of the column has to be set (in C and Java this is generally done with the null byte, in other languages a length field is set). When you display the table you can verify that the data has been loaded correctly, however the column length cannot be seen.

When you select this option the length of the column is displayed, along with the data. For instance FRED will be displayed as (4)FRED. This can be useful for verifying that the column lengths have been set correctly - if instead you see (20)FRED then you know that you are filling your database up with a lot of unnecessary blanks! This has happened on some projects we have been involved in.

Remove trailing spaces from string values

When this option is selected (which is the default), AQT removes spaces from the end of string values. De-select this option if you wish to retain these spaces.

This option is used when displaying data, plus the following AQT features:

• Data Export. This option governs whether trailing spaces are included when data is export
• Data Loader. This option governs whether trailing spaces are included when data is loaded from one table to another
• Data Compare. This option governs whether trailing spaces are included in the compare of values between tables.

For DB2 for iSeries use Column Headers

When this option is selected and you are displaying a DB2 iSeries physical / logical, AQT will use the Column Headers (rather than the Column Name) when displaying the table contents.

If the Column Heading is blank, AQT will use the Column Text.

For Oracle, use TO_CHAR with Timestamp and Interval columns

Most versions of the Oracle ODBC Driver do not handle Timestamp or Interval columns. When you try to display these, the ODBC Driver, and AQT, will crash. AQT has a work-around for this problem; when you display a table with columns of these types, and you have this option selected, AQT will enclose the timestamp/interval columns in a TO_CHAR function.

By default, this option is selected. De-select this option if you have a recent version of the Oracle ODBC Driver that can display these data types without problem.

How to display Bit / Boolean values

This specifies how bit / boolean values are displayed:

• Checkbox. This is default. The values are displayed as a checkbox in the grid.
• 0 or 1
• False or True

When Checkbox is used, you cannot see whether a value is Null. The other two options will show this.