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 opening the Query Builder tool. The application toolbar includes a button to open the tool. When initially opening the tool, it will open a database connection to the database server currently selected in the server browser. Alternitavely, 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 opened, 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 of 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. The Windows menu has the option "Limit Add Table to User Schema". This will limit options in the schema combo box to the user schema. This can provide a performance boost in scenarios when the database has a large number of user schemas and tables, by limiting the list of tables and views to the users objects.
Frequently used tables can be added to the shortcuts toolbar when it is enabled in the application 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. Right Clicking on a shortcut allows editing of the shortcut's Table properties. Clicking on a shortcut allows you to quickly add frequently used tables to your queries without browsing for them. |
|
||||
| 3. Creating JOINs with drag and drop - JOINs for your query are created by dragging table columns in one table entity and dropping them off on columns of another table entity in table 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 which maps to LEFT and RIGHT JOINs. 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 written as changes are made. |
|
||||
| 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 entering text within the Alias column inside the Columns Pane. The SQL pane updates instantly to show the aliases in script form for review. Syntax highlighting and formatting within the SQL Pane is set within File->Options->SQL Editor and File->Options->Formatter. Insert rows in the columns pane to use expressions. Within the columns grid pane, rows can be inserted by selecting a row, then right clicking to Insert. Once a blank row is inserted, Expressions can be entered in the blank row. The order that these rows appear can be shifted by using the moving options presented in the right click menu for moving a selected row up or down. Expressions can be as in-depth as needed, and when executed, will have their results appear in the query results pane. |
|
||||
| 5. Adding Filter criteria - You may add filters to your query by adding criteria to the [criteria] column of the columns grid pane. The pane may be in a [Quick Criteria Mode], or in [Advanced Mode]. You may choose from the menu Window->[Quick Criteria Mode] to change which mode you are in. In the [Quick Criteria Mode], you have the [Operator] and [Criteria] columns where you may choose the operator from the list (LIKE, =, IN, IS NULL, IS NOT NULL, NOT IN, >, <, <>, >=, <=, BETWEEN) or type in a custom operator, and then indiate the value you want the operator to match on. The [Advanced Mode] offers a [Criteria] free form column for operator and values, and an extended list of [Or...] columns which are OR'd together. Each 'Or...' column in the columns pane defines additional criteria. If you would like to add multiple filters on one column which have different criteria or operators and are AND'd together, you may right-mouse-click in the Column Grid and select "Insert" which will give you a new empty row. You may then select the column name in the [Column] column and apply the next criteria. You may add multiple rows for the same column and apply a different criteria for each, to give you multiple criteria on the same column that are AND'd togehter. |
|
||||
| 6. SQL Generation options - The toolbar has a Filter icons which provides the options : Quote Identifiers, Fully Qualified Tables, Database Qualified Tables that define how the SQL is generated. The Auto Joins on Foreign Keys determines whether JOINs are automatically created when a table is added to the query which has a foreign key relationship with one of the other tables. |
|||||
| 7. 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 Files 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.
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 Files Browser, or as part of a script directory tied to a database server connection within the Schema Browser. Option to save queries to SQL History: This option can be enabled/disabled in File->Options->Table Editor. When the option is enabled, queries generated in Query Builder are saved to SQL History after being executed and commited. With the Full (default) transaction type all of the queries are saved as one entry, otherwise queries are saved by batches being commited. If a query or a batch fails, including the case when a user cancels it, further queries are not executed and not saved. |
|||||
| 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 way as executing a query from within a Query Analyzer window. When a query is executed, its results appear in the Results Pane at the bottom of the Query Builder. Results can be viewed as Grid Results or Pivot Grid Results. Errors and messages generated during execution can be viewed in the Messages tab beneath the results pane.
Charts can be generated from Grid Results and Pivot Grid Results by clicking on the Chart icon at the top left of the Results Pane once results have been generated. For an in-depth description of Aqua Data Studio's charting abilities, see the Charting Tools in ADS and Working With Pivot Grids sections of the documentation |
|||||
| 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.
Note: The Table Data Editor uses the primary key or any unique constraint to identify the row in the result which it will generate UPDATE statements for. If your resultset doesn’t have a primary key or unique constraint, you will be prompted to define a primary key in the primary key tab of the Table Data Editor. |
|||||
| 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 - The Query Builder also allows you to write GROUP BY queries. From the Menu, select Query->[Group By] to enable the [Group By] column in the columns grid list. This allows you to change GROUP BY options for each of the columns. You may select the "Group By" option on the columns you want to participate in the GROUP BY clause, and select an aggregate function (AVG, COUNT, COUNT_BIG, MAX, MIN, SUM) on the columns you want to select and aggregate on. With these options you may quickly create a GROUP BY query. | |||||
| 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. |
|||||
| 13. Generate AquaScript - To generate an AquaScript from a built query within the Query Builder, select File -> Save Results and click the Generate AquaScript button. To preview the AquaScript before generating it, click the AquaScript tab. This AquaScript will generate these results when it is executed within the AquaScript Editor. Clicking Generate AquaScript prompts for saving into an AquaProject. See the AquaScripts section for more details on working with AquaScripts. |