GUI Query Builder - Options Tab

Access this from the Options tab of the Query Builder.

This tab has a large number of options which specify how the SQL statement is formatted.

Some options which may not be obvious are explained below.

Line Breaks

This specifies where AQT will place line-breaks in your SQL

Case

These options specify the Case (Upper / lower / sentense) of various parts of your SQL

Join Syntax

For some databases (Oracle, SQL Server, Sybase, Informix, SQLBase), you have a choice about what syntax is used for the join SQL. These database have their own (Native) syntax for joins in addition to the standard (ANSI) syntax.

The options here allow you to specify which join syntax is to be generated by AQT.

Note that if you select the Native join syntax (generally the second of the two options given), not all joins can be specified:

• most databases do not have a native join syntax for Full outer join
• Informix does not have a native join syntax for Right outer join

Table-ID

These options specify how the Table-ID (a.k.a. correlation name) is to be constructed.

• If AQT has reverse-engineered a query it will use the table-ids as in the parsed-query.
• If any new tables are added to the query, AQT will generate the table-id based on these options.
• If you change any of these options, AQT will regenerate the table-ids based on the new options.

Column references

When you have an Order By or Group By clause, there are a number of options setting how columns are specified in the Order By and Group By clauses.

This is best explained with an example. Take the following query, which you wish to order by the first column:

Select Customer as Cust, Customer_Name as CustName from Customer_Details

• With Column Names specified you will get: Order By Customer
• With Column Numbers specified you will get: Order By 1
• With Column Aliases specified you will get: Order by Cust

Some of these options may not be valid for your database. For instance, many databases do not allow you to use Column Aliases in the Order By and Group By statements. AQT will not enforce these rules.

Other

Use Select * When Appropriate

By default, AQT will generate the SQL as “Select * from table” when this is appropriate. It is appropriate when

• the query includes all the table columns
• the columns are in the same order they are in the table
• no column functions or correlation names are used.

If you de-select this option, AQT will include all the columns, instead of an *.

Always Qualify Table Names

When this option is selected, AQT will always qualify a table name (with the schema name). When the option is not selected, AQT will not qualify a table name if the schema is the same as your user-id. This is useful if you are do not want the schema names included in the query you are building.

Ignore Case in Correlation Names

This applies to the Display Names you set up for columns. It governs how AQT handles a display-name that has lower-case characters – if you want the case preserved you must de-select this option.

For instance, suppose you give your Customer column a display name of Cust. With this option selected (the default), AQT will generate the column clause as CUSTOMER as Cust. Some databases ignore the case in the correlation name and will display it as CUST. If you want the case used as you have entered it, de-select this option. AQT will then generate the column clause as CUSTOMER as “Cust”. The database will display the column as Cust.

Save Table Positions with Query

When this option is selected, when AQT saves the query, it will save information about the position and size of the table-windows in the GUI display. This is useful if you have moved and resized the tables in the GUI, and you want this information saved for the next time you work on the query.

When this is selected an AQT control statement --aqt queryinfo will be included with the query SQL.

Include Columns with New Tables

By default, when you add a new table to the query AQT will add all the table columns to the query. De-select this option if you do not want all the table columns to be added – eg. when you add new tables, no columns will be added to the query.

Show columns in alphabetic order

When this option is selected, table columns will be shown in alphabetic order (as opposed to the order they are defined in the database).

Changing this option while you are building a query can give unpredictable results. You should click on Save as Default Options, close the Query Builder then open it again.

Size of Tables

This option gives the size of the tables when you add a new table to the query. The default size is 200 x 150 pixels. You can increase these sizes if you want your tables to open with a larger size.

Qualify columns when Table Id is blank

This option is only applicable when the Table Ids for all the tables are blank and there is more than one table in the query.

• if this option is selected (the default) all columns will be qualified with the full table names
• if this option is not selected, the columns will not be qualified.

Quailify columns when Table Id is blank

This option is applicable when you have more than one table in your query, and the table-ids for the tables are blank.

When this option is selected, the columns in your query will be qualified with the names of the tables. This option is the default.

When this option is not selected, the columns will not be quailifed. This can arguably make the query more easy to read, however the query will fail if one of the columns exists in more than one table (as the column will not be uniquely identified).