|
|
|
|
|
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.
Type it into the textbox and do one of the following
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.
You can build your SQL by typing the SQL statement into the SQL window.
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.
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.
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.
The Run SQL window has a query-table associated with it. The name of this table is given in the window title.
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.
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.
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.
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.
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.
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.
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.
