Running SQL

From the Database Explorer window, clicking on Run SQL opens the Run SQL window. This is one of the main windows in AQT, and offers you an enormous amount of power and functionality.

The Run SQL Window provides you with a text box for entering and running SQL statements.

• There are four ways to run your SQL statement, depending on the way you like to work.

  Type it into the textbox and do one of the following

  • click on Run
  • use the Run icon
  • use the menu: Run > Run SQL
  • F5
• You can run action SQL statements (Update, Create, Grant etc) as well as Select queries.
• You can run stored procedures
• You can run multiple statements including a script file containing thousands of SQL statements.
• Queries can contain Substitutional Parameters. These are prompted for when your SQL is run.
• Once you have run your query the Data Display window will display the results of the query.
• Use Export to > File (or F7) to export your query data to a file.
• There are a number of options about the layout of this window

Run SQL toolbars

The Run SQL window has two toolbars - a main toolbar and an editing toolbar.

The editing toolbar contains functions useful for editing the SQL text:

Both these toolbars can be customized to have a difference appearance and selected toolbar buttons. It is also possible to drag the editing toolbar to be on the same line as the main toolbar.

Some tasks available in the Run Sql Window have shortcut keys.

Building your SQL

You can build your SQL by typing the SQL statement into the SQL window.

• right-click the SQL window, or use the edit toolbar, for common editing functions
• click on Build Query to build your query using the GUI Query Builder
• select Edit > Edit SQL using editor to edit your SQL using Notepad
• you can save and retrieve queries

Lower panels

Save keystrokes or typing errors by using the panels in the lower part of the window, which contain common SQL keywords and operators, plus the column list of your query table.

• Clicking on an item will add the text to your SQL, except for the column list, for which you have to double-click a column name (a single-click just selects it).
• You can right-click a column-name – AQT will then show you a drop-down list of sample column functions. Selecting one of these will add the column plus function to your SQL.
• You can add multiple columns to your SQL. Select multiple columns from the listbox (either by dragging the mouse down, or by using ctrl and shift), right-click then select Add Columns to SQL. This will add the column names to the SQL as a comma-separated list.
• You can change the entries that appear in these listboxes. This is described in more detail in Other options in the Run SQL window.

Get Values for Column

Once you have “selected” a column (by clicking on it), you can click on Get Values for Column to get all the distinct values the column takes. These will populate the right-hand panel. Clicking on one of these will add the value to your SQL statement (for instance as part of a Where clause). Quotes will be added to the value if required.

Extended Get Vals is like Get Values for Column, except that it gives you more options about the values retrieved.

Get Constants gives you a number of sample constants for your selected column. These are mainly useful for Date/Time/Timestamp data types, as it gives the in-built date/time constants, and shows you the format of date/time literals.

For Oracle date columns you get an option Add To_Date. When this is selected, the TO_DATE function will be added to any date values selected from the Get Values list.

Select the database

You use the drop-down listbox to specify the database the query is to run against. By changing this, you can very easily run your query against a number of different databases.

Query table

The Run SQL window has a query-table associated with it. The name of this table is given in the window title.

• The column-list for the query-table is given in the middle of the lower panes
• When you click on the Reset SQL icon, the SQL will be changed to Select * from table-name
• You can switch to another query-table by clicking on the Table menu icon (or hit Ctrl+T or the table icon). This will take you to the Switch to another Table dialog where you can select another table (in either this or another database). Alternatively you can click on Copy Table Name to just copy the table name (you can then paste this into your SQL).
• The Table menu item and icon have a list of the last 10 tables you dealt with (though it doesn’t remember the database they were in). This makes it very easy to switch between tables you commonly deal with.
• When you switch to another query-table, your SQL will be set to Select * from table-name
• You can display the contents of your query table with Table > Display Query Table (or F9).

Build Column List for Query Table

If you have more than one table in your SQL, you can select which table has its column list displayed. If you click on Table > Build Column List for Query Table (or F2) AQT will parse your SQL looking for the tables in your SQL. If it finds one, this will now become your query table and the column list in the lower panes will be for that table. If there is more than one table in your query, AQT will give you a popup list of the table names; select one of these.

In a join, your tables are often identified with a table-id (also called a correlation name). AQT will find these when it is parsing your SQL; when you select a table it will place the table-id in the textbox under the column list. This table-id can then be included with the column names when you add them to the SQL. This feature is very useful when building multi-table queries, as it makes it easy to add fully-qualified column names.

If there is a table name in your SQL but AQT does not find it (which can happen if it is within a sub-select statement), double-click to highlight the table name then hit F2. If this table has a table-id, make sure you include this in the text you highlight.

By default, AQT will show the columns in the order they are defined to the database. You can display the columns in column-name order by selecting View > Display columns in alphabetic order. This option is remembered for your AQT session, though is not saved between AQT sessions.

SQL History

AQT maintains a history of queries run. Clicking on File > SQL History (or Ctrl+H) will show you the SQL history from which you can retrieve a previously-run query. This history is saved to disk, so is retained between AQT sessions.

If you want a more comprehensive record of the SQL you have run, you can use the Auditing feature.

Analyzing your data

AQT provides methods for making simple analysis of your table and columns. These options are contained within the Analyse menu item, or the Analyse icons.

• Number of Rows will run a query which gives you the number of rows in your table.
• Selecting a Column then clicking on Number of Distinct Values of Column will show you just that.
• Selecting a Column then clicking on Analyse Column will show the values the column takes, and how many times each value occurs. This provides a very useful mechanism for quickly understanding the data held in a column.
• Selecting a Column then clicking on Duplicate Values of Column will show the values of the column which occur more than once. This can be useful is you want to know “how unique” a column is.

The Analyse Column and Duplicate values of Column functions can be used be used with multiple columns from your table. Using the Ctrl and Shift keys you can select multiple columns from your column list then run the analyse functions.

Table Analysis gives more details, and describes analysing multiple columns.

Syntax highlighting

AQT uses a third-party control for displaying the SQL text. This control is SourceView from Tetradyne Software (see http://www.tetradyne.com). This control will highlight various SQL keywords and identify comments, strings, numbers, and brackets. You can configure the syntax highlighting: see Options > Run SQL > Syntax Highlighting for details.

Search Table

You can quickly build a simple search on your table by clicking on the Table Search icon (or Table > Search Table). This opens the Search Table window, where you can quickly define a number of search conditions.

Click OK to close the Search Table window. The search condition will be displayed in your SQL statement in the Run SQL textbox. You can then amend the SQL further or run it.

GUI Query Builder

If you want a more comprehensive facility for building a query, click on the Build Query menu item or icon to go to the GUI Query Builder. AQT will parse the SQL you have in this window and display it as a GUI query. After you have amended the query in the GUI Query Builder, you can return it to the Run SQL Window.

You can use the GUI Query Builder to build just a selected portion of your SQL (such as a sub-select statement). Select the piece of SQL then click on Build Query. Only the selected SQL will be passed to the Query Builder.