Gui Query Builder - Options Tab

Access this window from the Options tab of the Gui Query Builder. Refer to that section for general information on the features of the Query Builder.

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

Most of these are obvious and are not described in detail. You can see what they do by selecting them and seeing the effect on your SQL.

Some options which may not be obvious are explained below.

Save / Load options

The options given here are relevant only to the current query.

• Save as Default Options will save these options as the default options.
• Load Default Options will set the query options from the default options.

Statement type

AQT can be used to build more than just Select statements. It can also build:

• View Definitions
• Select INTO statements (Sybase only)
• Any other SQL statement that contains a Select statement. This is explained further on in this section.

If you have built a query, you can change your query to one of these other types. Change the Statement Type then enter one of the options relevant for that type:

• For a Select into statement (which creates a temporary table), enter the name of the temp table
• For Create View statements, enter the name of the view. There are two styles of create-view statements – with or without an explicit column list specified. Select and de-select this option to see how this affects the generated SQL.
• For Other statements, specify the "wrapper" for the query. The wrapper is the SQL statement which contains your query. When coding this wrapper text, specify $sql where the query-sql is to be placed.
• Apply. Click on this button to regenerate the SQL based on the selected options.

Join Syntax

For some databases (Oracle, SQL Server, Sybase, Informix, SQLBase), you have a choice about what syntax is used for the join SQL. Before the SQL syntax for joins was standardised, these databases developed their own (Native) join syntax. Since then they have adopted the ANSI join syntax (though this will only be available to you if you have the release of the database that support this).

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 (as we don't want to have to change AQT every time a new release of a database comes out).

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 userid. 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 2500 x 2000 pixels. You can increase these sizes if you want your tables to open with a larger size. The sizes specified must be between 1000 and 10000 pixels.