8.1 Workspace Reference (Revision #77)

The table shortcut toolbar is a very useful item for people who wish to query the same tables often. You can simply right-click on a table object and select the [add shortcut to toolbar] to add that table's shortcut to the toolbar.  Once you have a table shortcut you may right click on that shortcut and change the shortcut properties. For example: if you create many queries on the same tables and wish to always start with them, you could change the properties of their shortcut [Key Binding] to "U" and check the "Clear tables before adding" checkbox. Every time you type U, your current query will be cleared and you will be starting with a new query.  

The shortcut toolbar settings are saved even when Query Builder is closed, so your table shortcut toolbar will still have the quick table reference when you re-open the Query Builder.

Loads an existing Query Builder Document (.xqb file) into the Query Builder.  If the current database connection of the Query Builder is not that of the document, Query Builder will load the connection information from the document.  This is the only method to change database connections in the Query Builder without reloading the Query Builder.

After creating a query you may save it as an .xqb file.  The file contains local refrence pointer information used by Query Builder to discover database and schema information, but does not actually save any database specific information.  So, if you wish to save an .xqb file and reload it on a diffrent computer you will need to make sure that the local reference pointer information is correct.  You may do this by opening the file in a text editor and checking it.   If you only wish to save the sql statement you may "Save sql as".  This will save the sql statement of the object to an .sql file in a text format.  You cannot load a .sql file into the Query Builder.

Save Results:

You may also save the result set of the query to file in many diffrent very useful formats. The encoding and platform can be specified or left as default to the current machine but may also be changed if you plan on sending the file to be used by someone in a different country or operating system.  The results type can be of the following: Grid Results, Pivot Grid Results, Text Results, Client Statististics or Explain Plan; with the following data formats: HTML, Excel, XML, Insert or Delimited

• Grid results will give you just the data from the sql statement in the chosen format.  Html, Excel, XML and Delimited are basic formats for data display options.  The Insert format is an interesting way of saving the results.  This option can be used to save scripts that would create a new table containing the results as its data or to create scripts that would insert the results into an existing table.  You will need to make sure and specify the schema and table name and type of line execution plan accordingly with the type of database you are working with.  If you do not, the scripts created will not work properly.  


• Pivot Grid Results works very similar to Grid Results unless you save it to excel.  Saving to excel will also save its pivot grid information if any was created.  


• Text Results will create a file with the complete server text output. including the sql statement, column headers, query data results, any errors that would accur from the sql or exceptions generate the application. you can uncheck the [ ] include sql statement if you don't wish to have the sql statement saved.  


• Client Statistics will give you specific statistics on the query run.  Including the count and type of queries executed; rows affected; transactions; time required to execute server and client side; wait times, etc...  

Explain Plan does not currently process anything within the Query Builder.

The Group By column allows the user to give grouping information for columns or to apply data aggregate functions to a column.  Data Aggregate functions compute, from a group of rows, a single value for a designated column or expression. This provides the capability to aggregate data, thereby enabling you to perform statistical calculations across many rows with one SQL statement.

The indexes pane holds the information on all the indexes that are associated to the tables associated to the query being built.  On the left side you have the indexes name and parent table with its properties. On the right side you have the columns and sequence to which the index is created.

Indexes are only used when an index's field with leftmost prefix of an index is in the where clause. example:
    multi-column index on field1 and field2
        select * from table where field1 = value1;   the index will be used.
        select * from table where field1 = value1 and field2 = value2;  the index will be used
        select * from table where field2 = value2;  the index will not be used

When scrolling through the indexes, in the columns pane you will see the index and index seq fill with corresponding values.  This will help you visualize the sequence of the index to make sure you are taking advantage of the indexes on tables when creating criteria.

• Quote Identifiers: This option places database specific identifiers around all fields in the sql statement. This allows for name spacing in tables and fields names. There is no harm in using this option and it is advised to keep it on to insure no sql syntax errors.


• Fully Qualified Tables: This places table schema information in the sql statement, allowing for multi schema selection and joining of tables.


• Database Qualified Tables: This option places database information in the sql statement allowing for multi database selection and joining of tables.


• Auto Join on Foreign Key: This option auto joins tables when there are foreign key associations between the tables when the tables are added.

Databases can be set up to disconnect idle sessions after an alotted amount of time or the network connection may be lost.  This button provides a fast and easy way to attempt to re-establish your connection to the database if you lose your connection for any reason.

Columns Pane: Displays column information on the tables visible in the Diagram Pane.

      The column pane is where most of your clauses are created.  When you check fields off in the visual tables they will be placed into rows in the column pane.  Here you will be able to work with the fields in the query to modify your select statement, where clause, group by clause and order by clause. 
      In the columns cell you can work with the field itself using functions on the field. 
      You can give the field an alias by entering the name in the alias cell. 
      The sort type and sort order cells are inner twinded, if you give a sort order you will need to give it a type; it will default to Ascending.
      The group by clause is used to either group by or assign an aggregate function to the field. 
      The criteria cell is what is used to create the where clause of the sql statement.  If you just wanted to use a fields for the where clause you can uncheck the Output box in the row.  This will remove the field from the select stament.