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.
The options given here are relevant only to the current query.
AQT can be used to build more than just Select statements. It can also build:
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 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:
These options specify how the Table-ID (a.k.a. correlation name) is to be constructed.
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
Order By Customer
Order By 1
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).
When your query is parsed into the Query Builder (for instance from the Run SQL window) it may be preceded with Comments or AQT scripting statements (such as queryparm or setparm). AQT will not remove these comments and scripting commands; they will remain with the query. You can view or amend these in the Comments and Commands box.
AQT will only retain comments which precede your SQL. Comments that are contained within your SQL query will be removed when AQT imports the query into the Query Builder.
Use Select * When Appropriate
By default, AQT will generate the SQL as “Select * from table” when this is appropriate. It is appropriate when
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
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.
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).
This is a clause that can appear between the SELECT keyword and the column list.
Example for Teradata and SQL Server: TOP 50
This is a clause that is added to the end of your SQL.
Examples are: FETCH FIRST 1000 ROWS ONLY, OPTIMIZE FOR 1000 ROWS, WITH UR