The most widely used window of Aginity Workbench application is the Query Analyzer window that allows exploring the database schema and executing SQL queries.
Query Analyzer is the main window that allows you to explore your database schema and execute SQL queries.
You can simultaneously connect to several database servers. A new Query Analyzer window is opened for each new connection.
You can use the Ctrl+Tab key combination or the Window menu for switching between several Query Analyzer windows.
The Query Analyzer window consists of three major parts: the Object Browser, the SQL Editor, and the Result Set.
If you use multiple monitors, you can move the Query Analyzer window to one of them. On the View menu, click Make Standalone Window and drag the window to another monitor.
The left section of the Query Analyzer window is called the Object Browser.
You can manage the visibility of the Object Browser. Press CTRL+B to hide or display the object tree.
By right-clicking a database, table, or another object, you can access functions specific to the corresponding object type.
The Object menu on the menu bar provides the same functions, as the shortcut menu available by left-clicking a tree node.
The following are some of the available functions, by object type:
You can create and apply an entity filter to the list of objects in the Object Browser to display the objects that are relevant to you and hide all other objects. You can also turn on a default filter to apply it automatically each time you connect to the target database.
If you don't see some of the objects in the Object Browser, they might be hidden by an applied entity filter.
Remove the filter. If a default filter has been turned on, it will be applied to the Object Browser each time you open a new Query Analyzer window or connect to a target database. You can turn off the default filter altogether.
The section in the upper right of the Query Analyzer window is used for editing SQL scripts.
To add a new tab to write a SQL query in, click New tab. You can also add a new tab by pressing CTRL+T if the Query Analyzer window is currently open.
To switch between several tabs in the active Query Analyzer window, press CTRL+F6 and CTRL + SHIFT + F6.
By right-clicking anywhere in the SQL Editor, you can access a number of useful additional features and SQL query management options. For example, you can override the row limit for the result set, use syntax highlighting and the code auto-complete capability, and more.
If the name of a database entity or an entity column selected in the Object Browser needs to be inserted into the SQL Editor, double-click the column. The column name will automatically be inserted into the spot where the cursor is currently positioned in the open Query Editor. You can choose from multiple text-formatting options by selecting Tools > Options >Object browser, and then selecting the most appropriate format from the On column double-click insert drop-down list.
In addition, you can drag any node from the Object Explorer tree to the SQL Editor to insert the text where the pointer is currently positioned, or at the point where the text is copied into the Query Editor (this behavior can also be customized using the Tools/Options windows).
If code autocompletion is enabled in the query options for a currently open SQL Editor tab, Workbench displays a list of suggestions for a name (for example, a table name) as you start typing it and press Ctrl+Space, as shown in the example below. These suggestions are possible matches from the list of database objects and other items in the current database.
Autocompletion does not limit the object choices to the current database schema that is selected in the Schema list. The result set from autocompletion is a distinct list of object names that you have access to with your credentials. Therefore, if Table A, for example, exists in two schemas (schema 1 and schema 2), autocompletion will only show Table A once. It's up to you to type the schema into the SQL Editor to make sure that you use the correct one.
If the application options specify automatic invocation of autocompletion, you can also display a list of related items after typing a full name and a period (.). For example, after you type a full table name and then a period, Workbench automatically displays a list of the columns from the table.
You can select other autocompletion options in the application options, such as whether to display a list of related parameters automatically or manually.
The SQL Editor can be split horizontally or vertically into two panes or into four panes. You may find a split view useful when working with a long, multistep query. You can see and edit two or more portions of the code in the panes that you otherwise couldn't see and edit in the SQL Editor at the same time. You can scroll the contents in one pane independently of the other panes.
To split the SQL Editor:
You can display information for a database object in a tooltip when you point to its name in the SQL Editor. For example, in your SELECT query on a SQL Editor tab, you can point to the table name to view a description of the table in a tooltip. To view details for a database object (for example, information about the columns of a table) in an expanded version of the tooltip, pause the pointer over the object name.
Tooltips are available for database objects if Enable Quick Info when the mouse hovers over a word in editor is selected in the application options.
The section at the bottom of the Query Analyzer window is called the Result Set. The section displays the SQL Query execution result in two tabs: the Output tab, and the Result tab.
The Output tab has three sub-tabs: Standard, providing a brief summary of the executed query, Text, providing a detailed description of the execution process, and Grid, allowing a grid view of the execution result.
The system displays the Output tab regardless of whether or not an error has occurred during a SQL script’s execution. If multiple statements were executed, and the server has returned several result sets, the result tabs are added for those queries that had been executed before the failed execution took place.
If you do not want the Text and Grid sub-tabs to be displayed in the Output tab, click Tools, select Options > Result set options /Output options, and then clear the Enable Text and Grid Output check boxes.
The Result Set allows you to drag a column from the Results tab, and perform grouping by this column. To do this, drag the column that you want to group by to the drag a column header here area.
Each result set grid can be represented in a single-row mode by using Show single row grid command from a context menu on a result set grid. Single-row mode transposes the result set so that result columns become rows and single row at a time is displayed in a transposed grid like shown on the figure below.
To navigate through the data use the buttons below the grid or Go to record input element for jumping to a particular record.
To define the desired formatting for numeric and temporal values in the data grid use Tools / Options.
You can export query execution results to the Excel 2003 XML or CSV formats. To do this, right-click anywhere in the Result tab, and then select Export.
The Menu bar provides access to several menus that are used to manage the bulk of the system’s functionality. The Toolbar is located under the Menu bar and provides access to the most frequently used options.
The Schema list on the toolbar displays the name of the current schema in the database. By default all queries are executed in the current schema. When you open the Query Analyzer window for the first time, this list shows the schema associated with the user name that you used to connect to the database. You can change the current schema by selecting an appropriate schema in the list.
The Path list on the toolbar displays a list of the default schemas set in the database. If the date being queried is not found in the current schema selected in the Schema list, Workbench will attempt to execute the query in the schemas listed in the Path list.
The Status bar is located under the Result Set section. It shows helpful status information. If you select more than 1 row in the result set, the Status bar can show the minimum, maximum, average, and other data. To make this feature available, click the drop-down arrow in the right side of the Status bar, and select the options that you want to be displayed.