Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
| 1. Opening a Query Builder Window - Visually building a query begins by openning the Query Builder tool. The application toolbar includes a button to open the tool. When initially openning the tool, it will open a database connection to the database server currently selected in the server browser. Alternaitavely, you may right mouse-click on the server desired and select the "Query Builder" menu item to launch the tool. Select an object in the Schema Browser, then click the Query Builder Icon or right click the object in the Schema Browser and select Query Builder from the menu (Ctrl + Alt + Q) | |||||
| 2. Adding Tables To the Query - When the Query Builder is initially openned, the add table dialog will open, allowing you to choose tables you would like in your query. Having the Query Builder automatically display the Add Table dialog at startup is optional based on File->Options->General->Query Builder->[Show Add Table Dialog at startup]. You may also choose the "Add Table" menu item in the toolbar to add more tables at any time if necessary. The Add Table chooser allows you to automatically include columns in the query, or you may choose them individually from the table entity in the diagram by clicking on the check box left to the column. The table entity contains a list of columns within the table and one extra row to indicate all columns of the table. The primary key columns are bolded, and columns participating in indexes are in italics.
Frequently used tables can be added to the shortcuts toolbar when it is enabled in the applicaton menu under Windows->[Show Table Shortcuts Toolbar]. Right clicking the title bar on a table entity in the diagram pane allows adding a shortcut. Clicking on a toolbar shortcut for a table adds it to the diagram pane without the user having to browse for it. This allows you to quickly add frequently used tables to your queries without browsing for them. |
|
||||
| 3. Creating JOINs with drag and drop - JOINs are created by dragging Table Columns and dropping them off on Columns of other Tables in the Builder Diagram Pane. JOINs are indicated by connecting lines. JOINs can be removed by right clicking the line and selecting Remove. If the JOIN icon on the line is first selected, then right clicked, menu options allow selecting all rows of either/both tables in the JOIN. The JOIN icon changes to indicate all rows of a Table have been selected. Within the SQL pane, the type of JOIN and its details are writen out as SQL for review. |
|
||||
| 4. Adding Columns to the Query - Columns are added by checking their check box on the left-hand side of a Table Entity. When a column is checked, it appears in the Columns Pane. Aliases can be created and edited by editing the Alias column within the Columns Pane. The SQL pane displays the aliases in script form for review. Insert Rows in the Columns Pane to use Expressions - Within the Columns Pane, Rows can be inserted by selecting a row, then right clicking to Insert. Once a blank row is inserted, Expressions can be entered which will appear in the Query Results Pane, and can be as in-depth as needed. |
|
||||
| 5. Adding Filter criteria (Quick Criteria Mode) - The Filter icon within the Query Builder allows for filtering of Quote Identifiers, Fully Qualified Tables, Database Qualified Tables and Auto Joins on Foreign Keys. Criteria values can be inserted within the Columns Pane (a list of Operators is available) when Quick Criteria Mode for filters is selected. |
|
||||
| 6. Adding Filter criteria (Advanced Mode) - Unchecking "Quick Criteria Mode" from the Window Menu in the Query Builder Menu Bar enables the Advanced Mode. Advanced mode offers Criteria insertions, each 'Or ...' column defines additional criteria. Very complex filter criteria can be added to build out detailed queries. |
|||||
| 7. Saving Queries (SQL) - Queries can be saved by clicking the Save SQL icon (Ctrl + Alt + S) in the Query Builder Toolbar. This saves the query as SQL and can be edited further in Aqua Data Studio's SQL editor, either through inclusion in a mount in the Scripts Browser, or as part of a script directory tied to a database server connection within the Schema Browser.
Saving Query Builder files ( .xqb files) - Saving a Query Builder file (Ctrl +S) saves files with the extension .xqb. If a saved Query Builder file appears in the Schema Browser or Scripts Browser, double clicking it will open it within the Query Builder. Query Builder files can be edited in the XML Editor to alter connection information. This can be very useful for sharing .xqb files with users who may not have the same names for their database connections, but do connect to the same database server and database. |
|||||
| 8. Executing Queries - Queries are executed by clicking the the Execute icon (Ctrl + E) or by right clicking within the Diagram Pane and selecting Execute from the menu. Executing a query within the Query Builder functions the same was as executing a query from within a Query Analyzer window. |
|||||
| 9. Executing Queries for Editing - Executing edits is useful for altering table data, inserting rows and other information. Execute Edit is performed by (Ctrl + Alt + Enter) or by clicking the Execute Edit icon in the tool bar. The Table Data Editor appears when an Execute Edit is performed, allowing additional rows, data and other information to be added or deleted from databases through an Excel-like grid. |
|||||
| 10. Saving Results to Excel/CSV/Insert - Once a query is built and executed, the results it generates can be right clicked and saved as Delimited Data, INSERT statements, XML Documents, HTML Documents or Excel Worksheets. Results can be viewed from within the Query Builder as Grid Results and Pivot Grid Results by clicking on the tabs which appear at the bottom of the Query Builder Window. Messages generated during query execution (including execution time, warnings and errors) are viewable underneath the Messages tab to the right of the Grid Results and Pivot Grid Results tabs. |
|||||
| 11. Group By Examples - From the Query Menu, Group By can be enabled. Within the Columns Pane, pick a column to Group By. Once a column has been selected to "Group By", Aggregate Functions (AVG, COUNT, COUNT_BIG, MAX, MIN, SUM) can be chosen for that column. Results generated with Group By options then appear in the Results Pane when the query is executed. | |||||
| 12. Index Assistant - The Index Assistant helps you create queries which use indexes efficiently. When the index assistant is enabled, a list of indexes which exist on all the selected tables will be presented. If an index is selected, the tool will color code the column criteria in all columns selected and enumerate the column index count to allow the user to quickly identify if the index will be used in the query. If the column index enumeration is not in numerical order, the index will not likely be used. |