Running SQL & Queries

Query Builder Functions

Table of Content

Table of Content

Table of Content

GUI Tab

Access this window from the Gui tab of the Query Builder.

The Gui tab shows you:

  • the tables in your query. You can move these around, resize them etc.

  • the joins between the tables.

Adding Tables to the Query

You can add tables to your query in the following ways:

  • Add Table icon

  • Table menu item

  • Double-click the background

  • Right-click the background

Add Related Tables

This will add all tables known to be related to the currently-selected table. This includes:

If you only wish to select some (and not all) related tables, click on Add Table then go to the Select Known Relationships tab

A Table shows no columns?

If a completely empty table-box is displayed, this means that AQT has been unable to find the columns for the table. This may happen because the table is held on a different server, or because the table-name has been qualified in a way that AQT is unable to interpret.

Joining Tables

If you use Add Related Tables or Add New Table > Select Known Relationship, your tables will be joined automatically by AQT.

Otherwise you will need join the tables manually. You can do so by dragging columns between tables.

Join only columns of same type

Generally, you should only join columns which are of the same type. If you try to join columns of different types, AQT will give you a warning message. You then have the option of aborting the join (in case you have made a mistake).

Multi-column joins

If your join is a multi-column join, you will need to repeat the join procedure for every column in the join. As you do so, you will get the message “This relationship has been added to the existing join”.

You get only one join-line between the tables, even though there are multiple columns in the join. This is in contrast with some other products, which show one join-line for every pair of columns in the join.

Tables relating to themselves

You cannot join a table to itself.

If a table has a relationship with itself (which may well be valid), you will need to add the table twice to the query and join these. Even though these two tables have the same name, they will have different table-id/correlation names, so they can be referred to without ambiguity.

Multiple relationships between two tables

Similarly, there can only be one relationship between two tables. If TableA has two relationships to TableB (again, this is quite normal) you must create two instances of TableB in the query and join TableA to each of these.

Viewing or Amending Join Information

If you click on a join-line, the columns which are in the join will be highlighted. This is a very quick way to see the definition of join.

For other join options, right-click the join. You will shown a pop-up menu with options:

  • Join Details. Takes you into the Modify Join window, where you can view or modify the details of the join.

  • Delete Join.

  • Remember. The join will be saved as a user-defined relationship.

  • Join Type. This option allows you to select the join type (Inner join, Outer Join etc).

Removing a table from the query

You can do this by clicking on the Close (X) button on the table.

Selecting query columns

You can specify whether a column is in the query by checking / unchecking the column.

Click All and None to quickly select that All or None of the table columns are in the query.

To specify other options about the columns, go to the Columns tab.

Table / Joins Tab

Access this window from the Table/Joins tab of the Query Builder.

This shows you the tables and joins in your query, and has some further options not possible from the GUI window.

Table Id

Tables can have a table-id, which is an short name for referencing the table. The table-id is also referred to as the “correlation name”. Using table-ids makes the SQL less voluminous and easier to read.

If a table appears twice in the query, it is essential to have a table-id to distinguish which of the two instances of the table you are referring to.

AQT will by default generate a table-id for the tables. To change this, click in the Table Id column and enter/amend the value. Then hit Enter or click elsewhere to effect the change.

The Options Tab allows you to set a number of options about how this table-id is generated.

New table

This allows you add a new table to your query.

After adding a new table to the query, you will be asked to specify a join for this table.

New Expression

See the topic Using Table Expressions for a description of this.

Delete

This allows you to delete a table from the query.

Joins

These are discussed in the topic Creating or Modifying Joins

Using Table Expressions

The Query Builder allows you to use a Table Expression in your query.

You will use a Table Expression when your data is to come from an SQL statement, rather than a table. Table Expressions are also known as Inline Views or sub-queries.

An example of a query which uses a Table Expression is

SELECT * FROM

Customers a,

(SELECT CustomerID, count(*) as num FROM Orders GROUP BY CustomerID) b

WHERE a.CustomerID=b.CustomerID

There are two “tables” in this query:

  • the first table is Customers

  • the second table is (SELECT CustomerID, count(*) AS num FROM Orders GROUP BY CustomerID)

This second of these is a Table Expression. Most databases support the use of Table Expressions, which provide a mechanism for developing powerful queries.

New Expression / Edit Expression

To specify a new Table Expression, click on New Expression. Enter the SQL text for the table expression.

The table will now appear in the query with the name (expression). If you create more than one Table Expression, the table-ids will differentiate them.

You can amend the table expression by clicking on the Edit Expression button.

New Expression (in QB)

You can create a Table Expression in another Query Builder window. If you click on New Expression (in QB), AQT will open a new Query Builder window in which you can create a query to be used as a Table Expression.

  • this Query Builder window will be labelled as Build Expression in the AQT window-list bar

  • the first line of the SQL text will be a comment that this is an expression for another Query Builder window

  • you can run this query, and generally operate this Query Builder window as per normal

  • as you amend the query, the query in the parent Query Builder window will be changed accordingly

Edit Expression in QB

Clicking on Edit Expression in QB will allow you to edit a Table Expression in a Query Builder window

  • if you are already editing that expression in a Query Builder window AQT will take you to that Query Builder window

  • if you do not have a Query Builder window open for editing that expression, AQT will open a new Query Builder window

Identifying Table Expressions

A Table Expression must generally have a Table-Id. AQT will normally generate one unless you have set the option not to use Table-Id (Options > Table ID > no table id), or if it is the only table in the query.

Any generated or summary columns in your Table Expression should be given names with the AS clause.

Invalid

no proper name for second column, difficult to reference

SELECT CustomerID, count(*) FROM Orders GROUP BY CustomerID

Valid

AS clause gives name to column

SELECT CustomerID, count(*) AS CustCount FROM Orders GROUP BY CustomerID

In order to determine the columns in the Table Expression, AQT does a “prepare” on the SQL for the Table Expression. Some databases do not support a standalone prepare, and may run the SQL; this might cause a delay when you enter or change a Table Expression.

Creating or Modifying Joins

The bottom grid of the Tables/Joins tab shows you the joins in your query.

  • New Join allows you to create a new Join. You may wish to use this function rather than dragging between columns on the graphical display.

  • you can change the Join Type by selecting from the drop-down list

  • to change other aspects of the join, click on Modify

Join Detail window

When you click on New or Modify, you will be shown the Join window.

Using the Join-columns grid

To specify the columns in the join:

  • click on the column from the From table

  • click on the column from the To table

  • as you do so, the columns will be added to the grid

  • to add another join column, click on another (empty) row in the grid and repeat the above process

  • if there are no more empty rows in the grid, click on Another entry

Auto Join

This can be a quick way of setting the join columns. If the columns you are joining have the same name in both tables, Auto Join will automatically add them to the join grid.

Join Expression

This panel allows you to specify some "advanced" join options.

  1. Sometimes you may wish to use a column function with your join columns. Example:

    Where UCase(TabA.CustCode) = UCase(TabB.CustCode)

    This can be done by using the Column Function drop-down boxes, or entering a column function

  2. You may also need to compare a join column to a particular value. Example:

    Where TabA.MonthlyRevenue > 100000

    To do this:

    • set the Join Operator to > rather than =

    • enter the constant in the Column Function box

Remember Join

Once you have defined the join as required, you can click on Remember Join for AQT to save the details of the join.

This will be saved as a User-Defined Relationship.

Columns Tab

Access this window by clicking on the Columns tab of the Query Builder.

With this window you can:

  • add or remove columns from the query

    You can do this by checking / unchecking columns in the panel on the left, or with the New or Del buttons.

    Use Mod to modify a query column in the Build Query Column dialog.

  • change the order of the columns in the query

    This is done with the Move Up or Move Down buttons.

  • specify a column function.

    Select one from the drop-down list, or enter a function. The column name will be substituted where you have a colon.

  • give your columns a Display Name.

    This is the name of the column when the query is run.

    Use a : where you want the column name included.

  • specify the order-by columns for the table. These give the order in which rows will be returned by the query.

    Click on Order By to select the column as a order-by-column. If you have multiple columns, they will be selected in the order you click them. Right-click Order By to decrease the columns order-by position. Click on the Delete button to not sort by this column.

    Click Asc/Desc to toggle between sort ascending and sort descending.

  • Show specifies whether this column will be displayed.

    You may have some columns which you need to have in your order-by list, but do not wish to include in the query. For these, de-select Show.

  • specify a simple Where clause (search condition) for the query.

    This is done with Where Op and Where Value.

    You can also specify Where conditions on the Where tab. This provides a more fully-featured method for building Where clauses.

Specifying Literals

You may want your query to return a literal value or system variable. Examples: 23, 'Unknown', CURRENT DATE, USER.

To do this, specify this literal value in the Function field. Because the value does not contain a colon, no column name will be added.

Alternatively, you can click on New or Mod to go to the Build Query Column dialog.

Adding a column to a query multiple times

Sometimes you may want to have a column in your query multiple times - for instance with a different function for each appearance. Example:

Select DATE(Update_Time), TIME(Update_Time) From Table

You can do this by clicking on New to create a new query column. When you use this, you can create multiple query columns using the same table column.

Summary Query

When this is checked, this specifies that you are building a Summary Query.

This is discussed in Summary or Group-by Query.

Other Features

Distinct

Use this option to display only distinct rows in your query. Duplicate rows will be removed. This option is generally not of much use if you are displaying all columns in the table. It is often used to display the different values a column can take. In this case you will specify only one or two columns in the query.

All

Use this option to specify the ALL option for your query. This option is the default so there should be no need to specify it.

Types

When this is checked, the column types will be displayed in the grid.

Desc

When this is checked the column description will be displayed in the grid.

Build Query Column

Use this Window to build or modify an individual Query Column. You open this window by clicking on NEW or MOD in the GUI Columns tab.

Summary Query

If you are building a Summary Query, the Function list will just be the aggregate functions (sum, avg, min, max).

Specifying a Query Columns as a Literal or System Variable

You can specify a literal, expression or system variable, by checking the Query Column is a literal box.

You can also use this method for specifying a sub-select or case statement. For large expressions such as these,click on the >>> for a larger box.

AQT provides no assistance for building a sub-select or case, though this may be done in a later release.

Summary or Group-by queries

Summary queries (also called Group-By queries) are used to summarise the contents of a table.

You mark a query as a Summary Query by selecting the Summary Query option.

Example of Summary Query

Suppose you wish to summarize your sales by Region and Sales Person

  • Region and Sales Person are the columns you are Summarizing By. You mark these in the grid by clicking on the Group By column

  • Sales is the column you are Summarizing. This is generally a numeric column.

    You need to provide a summary / aggregate function for this column - this specifies the statistic you will be computing - Sum, Average, Minimum, Maximum etc.

    There can be multiple

Building a SubSelect expression

Your query can return values which come from another table. It is also called a SubQuery.

Building a new SubSelect

This can be done in two places:

Amending a SubSelect

If you have a SubSelect expression in an existing SQL statement, then you can amend this as folows:

  • import the SQL into the Query Builder

  • go to the Column tab > select the column expression > Mod > Build SubSelect

AQT will parse the SubSelect and display it in the SubSelect dialog for you to amend.

Example

The following query displays Sales Details, but includes the customers full name, which comes from the Customers table.

Select ORDERNO, ORDER_DATE, QUANTITY_ORDERED, CUSTOMER,

(SELECT FULLNAME FROM CUSTOMER_INFO WHERE CUSTOMER=s.CUSTOMER)

FROM SALES_DETAILS s

The clause (SELECT FULLNAME FROM CUSTOMER_INFO WHERE CUSTOMER=s.CUSTOMER) is called a SubSelect clause as it selects data from another table.

Some rules about SubSelects:

  • they can return only a single column

  • they can return only a single row

In most cases, you would "join" the SubSelect to the outer table. This is done here with the clause WHERE CUSTOMER=s.CUSTOMER. This ensures that only a single value is returned per row in the outer table. This is often called a correlated subselect (or correlated subquery) as this Where clauses correlates / matches the rows between the two tables.

Building a Case Expression

A CASE expression is used to display different values depending on a set of conditions. It is a powerful expression and is widely in SQL.

Building a new Case Expression

This can be done in two places:

Amending a Case Expression

If you have a Case expression in an existing SQL statement, then you can amend this as folows:

  • import the SQL into the Query Builder

  • go to the Column tab > select the column expression > Mod > Build Case clause

AQT will parse the Case clause and display it in the Case dialog for you to amend.

Example - Simple Case

This example is of a simple Case expression, in which the conditions involve a single column. This is often used to translate the values of a column to a more displayable format.

SELECT tbspace,

Case datatype

When 'A' Then 'Regular'

When 'L' Then 'Long'

When 'T' Then 'Temp'

When 'U' Then 'User Temp'

Else 'Other'

End

FROM SYSCAT.TABLESPACES

Example - Complex Case

In this example the conditions are more complicated than just comparing a single column to a set of values.

SELECT EmpNo,

Case

When Salary > 100000 Then 1000

When Commision_Type = 'F' Then Salary * 0.10

Else Salary * Commision

End as Commission_Paid

FROM EMPLOYEE

Where Tab

Access this window from the Where tab of the Query Builder.

This window is used to build the Where Clauses comprising a Where Statement. The Where statement specifies which rows will be returned by the query.

Where Statements

A Where Statement consists of a number Where clauses, connected by ANDs and ORs; and indented within various levels of brackets

For instance, the following Where Statement consists of 4 Where Clauses

(name=’fred’ and state=’CA’) or (salary<10000 and name is null)

When you go the Where tab, you will be shown the individual Where clauses in your query.

Building a Where Statement

You build your Where statement in two steps:

  • build the individual Where Clauses by clicking on New. This will take you to the Build Where Clause window.

  • specify how the Where clauses are connected by specifying the Operator (AND/OR) and the bracketting

Understanding AND / OR

People new to SQL can sometimes be confused about the meaning of AND and OR as they can appear to have a different meaning to ordinary speach.

  • name=’fred’ AND state=’CA’ means "show all rows which have name='fred' and also have state='CA'".

  • name=’fred’ OR state=’CA’ means "show all rows which have name='fred' or have state='CA'".

So

  • AND makes the query more restrictive, so you will generally get less rows

  • OR makes the query less restrictive, so you will generally get more rows

Show Clauses as Numbers

When you have a complex Where Statement, it can be difficult to visualize how the clauses connect. In this case it can be useful to select Show Clauses as Numbers. When this is done, the Where statement is shown in the following format - this can be make it easier to interpret.

Disabling a Where Clause

You can disable a Where clause by checking the Disable check box.

This "removes" the Where Clause from the query, however it will remain in the grid and can easily be added back in.

Show What

If you are building a Summary Query, you will get another box:

This specifies whether the grid is to show your Where Statement or your Having Statement. This is discussed in Having Statement

Build a Where Clause

This window is used to build an individual Where Clause. You enter this window by clicking on NEW or MOD in the Where tab.

You may also wish to refer to our summary of Where Clause Syntax.

The layout of this window reflects the normal format of a Where clause, which is:

column_name operator value

Examples:

cust_name = 'Smith'

purchase_time > CURRENT TIME – 1 DAY

substr(cust_name,1,2) = ‘FR’

cust_type NOT IN (‘a’,’b’)

The column may have a Column Function applied to it.

Column Value

To specify the column value, you can:

  • enter the value in the box at the top. Edit Value will show you a larger box in case you need to enter a large value.

  • click on Get Values to see the existing values of the column. You can then select one from the list.

  • click on Extended Vals to get a fuller dialog for displaying column values.

  • click on Get Constants to see the common system variables (and other constants) for the column.

  • if your column is a Date, Time or Timestamp type, you will get a Select Date button to help specify a date value.

As you build the Where clause, the full text of the clause will be displayed in the Where Clause text box.

Quotes

This specifies whether the value in the Column Value box is to have quotes placed around it. AQT will (normally) be smart enough to set this appropriately for your column type. However in some cases you may want to set this yourself.

Build Sub-Select

A sub-selects allow you to compare your column to a value in another table.

Clicking Build Sub Select will take you to the Build Sub Select dialog where you can specify the sub-select.

BETWEEN Clause

When you have a BETWEEN clause you need to specify two values for the column (eg. BETWEEN 100 AND 200). You will get a dfferent style Column Values panel where you can specify these two values.

Enter the value in the box, or click Get Values and select from the dropdown list.

IN Clause

When you have an IN clause you can specify multiple values for a column - eg. IN (‘A’,’B’,’C’,’D’).

To make it easier to deal with a list of values, click on Build IN Clause. This will show the Build IN Clause dialog.

If you are entering the values manually, you must include the quote for each value. Use quotes is ignored in this case.

STARTS WITH and CONTAINS

For character columns, AQT will show you operators Starts With and Contains. These are not valid SQL operators, but make it easier for you to build the appropriate clauses using the LIKE operator.

Not comparing a Column

In some rare cases you may not be comparing a column, but are comparing a special register or constant. Examples:

USER = 'test'

CURRENT DATE > '2010-03-04'

23 > 56 (I've no idea why someone might want to do this!)

You can specify a Where Clause like this by entering the first value (USER, CURRENT DATE, 23 etc) in the Column Function box.

This is a HAVING Clause

If you are building a summary query, you will see another checkbox This is a HAVING clause.

When you check this box, you will be building a Having Clause, rather than Where Clause. The Column Functions will show column summary functions. For a Having Clause you must select one of these.

Add and New/Close

  • Add and New will add the Where Clause to your query, but keep the window open so you can add another.

  • Add and Close will add the Where Clause to your query, then close the window.

Building an IN clause

AQT provides a tool for building a Where clause which uses the IN operator.

And example of such a clause is:

CUSTOMER in ('GIGGLE','GOGGLE','4M')

In some cases one might have a large list of values in the IN list, and the Build IN List dialog makes it easier to manage this.

The Build IN Clause dialog is started by selecting Build IN Clause from the Build Where Clause dialog.

Pasting Values into the Available Values box

The Paste function allows you to add a set of values from the clipboard.

You might use this to, for example, add a list of values you have copied from an Excel worksheet,

When you click on Paste, you will be prompted for the delimiter used for the values you are pasting.

Building a SubSelect clause

A subselect is used when you wish to compare a column to the value(s) in another table.

For instance, the following query displays all customers who have ordered more than 100 items:

Select * From SAMPLE.CUSTOMER_INFO

Where CUSTOMER IN (Select CUSTOMER From SAMPLE.SALES_DETAILS Where QUANTITY_ORDERED > 100)

To build a Where clause like this, click on Build SubSelect from the Build Where Clause window.

Returning Single or Multiple Rows

The golden rule of sub-selects is:

  • If your Where operator is IN or EXISTS, your sub-select can return multiple rows

  • For all other operators, your sub-select can return only a single row

Example of using an Operator other than IN:

Where Quantity_Ordered = (select max(Quantity_Ordered) from SALES_DETAILS

You can build a sub-select such by specify Column of Quantity_Ordered and Function of max(:).

Building a Correlated SubSelect clause

A correlated sub-select is similar to a SubSelect, except that the subquery references columns in the main query tables.

Example: the following query displays orders where the quantity ordered for a part is greater than the stock level for that part.

SELECT * FROM Order_Line ord

WHERE Part_Number in (SELECT Part_Number from Part_Details part WHERE ord.Quantity_Ordered > part.Quantity_On_Hand)

The sub-select (using table Part_Details) references column Quantity_Ordered in table Order_Line in the main query.

EXISTS operator

When using a sub-select, in particular a correlated sub-select, the EXISTS operator is often used. This return True if the sub-select returns one row or more, otherwise returns False.

Example: to display Customers who have at least one Order, one would code:

SELECT * FROM Customer_Details a

WHERE EXISTS (SELECT 1 FROM Order_Details b WHERE a.cust_code = b.cust_code)

Note that the sub-select can only return one column. It is irrelevant what value is returned - EXISTS only checks whether any rows are returned. It is common, therefore, to just specify a constant in the column list. The above query uses 1.

To code a query such as this:

  • in the Build Where clause window, select an Operator of Other and select EXISTS from the dropdown list

  • click on Build SubSelect to build the subselect. You can either select a Column, or use a Function of 1.

The operator NOT EXISTS is also commonly used and return the opposite of EXISTS - eg. True if the sub-select return no rows.

Having Statement

When you are doing a summary query, you can also specify a Having Statement. This is like a Where statement, except that it involves a summary value, rather than a column value.

In the example of customer revenue, the following query shows you customer revenue when it is more than $1000:

SELECT customer_code, month, sum(revenue)

FROM Orders GROUP BY customer_code, month

HAVING sum(revenue) > 1000

Here the HAVING clause has sum(revenue), which can only be determined after the table has been summarized.

Having versus Where

The easiest way to understand the difference between a Where clause and a Having clause is as follows.

  • A Where clause specifies the rows in your table to be included in the summarization calculation

  • The Having clause operates after the summary calculation has been done. It specifies which of the summarized rows are returned by the query.

Visual Where

To make it easier to understand your Where statement, AQT shows you a visual representation of your Where statement:

  • click on there Where tab to see the Visual Where display for your query

  • as you change your Where statement, the Visual Where display will change

  • click on Show in larger window to see Visual Where in a separate window. This window will be linked to your Query Builder window and will be updated as you change your Where statement.

Understanding Visual Where

Visual Where shows your Where statement in a visual format. The following examples will help you understand how this is displayed.

Example 1

WHERE LOCATION = '234'

AND MGRNO is not null

This Where statement has 2 conditions. The database will return rows for which both conditions are met.

Visual Where will show this visually as per the diagram below.

The database will test every row against the conditions in the Where statement

  • each yellow box is a condition to be tested

  • if a condition is True, continue to the next condition.

  • if a condition is False, the path terminates and the row will not be displayed.

  • if you get to the Result node, the row will be returned by the query

In this example:

  • the condition LOCATION='234' is tested

  • if successful, MGRNO is not null, is then tested

  • if successful, the row will be returned in the result

  • if neither condition is successful, the row will not be included in the result

Example 2

WHERE (LOCATION = '234' OR LOCATION = '123')

AND MGRNO is not null

This is similar to previous Where statement, except that there are two conditions on LOCATION. A row will be displayed if either LOCATION='234' or LOCATION='123' (and MGRNO is not null).

Visual Where will show this visually as below. There are now two routes that can be taken to the Result node. If the LOCATION='234' test fails, then LOCATION='123' offers another route through to Result.

Example 3

This is a more complex Where statement with many conditions. However the same rule applies as per with the previous examples - if there is a route through to Result then the row will be displayed, otherwise it will not.

Visual Where window

This window is displayed in two circumstances:

  • from the Query Builder window when Show in larger window is selected

  • from the SQL window when View > Visual Where (Ctrl+Shift+W) is selected

When called from the Query Builder window, the Visual Where window will remain open and the diagram will be updated when the Where statement for the query changes.

When called from the SQL window, the Visual Where window is modal and must be closed before AQT can be used further.

Union Queries

The Query Builder has support for multiple Unioned statements.

An example of a Union query is:

Select * from SAMPLE.SALES_DETAILS_2015

union

Select * from SAMPLE.SALES_DETAILS_2016

union

Select * from SAMPLE.SALES_DETAILS_2017

union

Select * from SAMPLE.SALES_DETAILS_2018

This combines the results of multiple sets of data.

Creating a Union Query

Once you have built the first SQL statement in your query, you can add another statement with:

  • Union > Add statement

  • going to the Union tab and clicking on Add New Statement

Each Statement is Built Individually

With the Query Builder you build each statement individually:

  • use Next / Prev to move through the different statements in your query

  • the message in the status-bar at the bottom will show you which statement you are dealing with

  • the SQL in the bottom panel will either be the SQL for the statement you are dealing with, or for the entire Unioned query. Clicking Full SQL will switch between these.

  • the Union tab will allow you to view and manage the different statements in your query

Running the Query

  • if Full SQL is selected, Run will run the full query

  • if Full SQL is not selected, Run will just run the statement you are building.

When you Save the query, or copy it to the clipboard / SQL window, this will be the full query.

Advanced Union Queries

Union Operator

There are a number of different operators that can be used in Union queries

  • Union. Combine the results of the statements, with duplicates removed

  • Union All. Combine the results of the statements, with duplicates retained

  • Intersect. Only include rows which are returned by both statements.

  • Except. Include rows which are returned by the first statement, excluding those returned by the second.

Intersect and Except are only available for some databases. Oracle uses Minus rather than Except.

Example 1

To see which customers had sales in both 2016 and 2017, run the query.

Select distinct CUSTOMER from SALES_DETAILS_2016

Intersect

Select distinct CUSTOMER from SALES_DETAILS_2017

Example 2

To see which customers had sales in 2016 but not in 2017, run the query

Select distinct CUSTOMER from SALES_DETAILS_2016

Except

Select distinct CUSTOMER from SALES_DETAILS_2017

Braketing

When Intersect and Except are used, the order of the statements becomes important. In this case, statements can be braketed to force them to be processed in the appropriate order.

For instance, to see customers that had sales in both 2015 and 2016, but not in 2017, run

(Select distinct CUSTOMER from SALES_DETAILS_2015

Intersect

Select distinct CUSTOMER from SALES_DETAILS_2016)

Except

Select distinct CUSTOMER from SALES_DETAILS_2017

This gives different results if the brakets were different or not present.

AQT Support of advanced Union Queries

AQT can deal with queries such as this, as:

  • it supports the use of different Union operators

  • it supports the use of braketing

These are managed on the Union tab.

Union Tab

The Union tab shows the statements in your Union query and allows you to:

  • Add / Delete statements from your query

  • Change the Union operator

  • Change the order of the statements in the list. This is useful for an advanced union query.

  • Change the braketing of the statements. This is useful for an advanced union query.

Check Consistency

A Union query requires that the individual statements return the same number of columns, and that these columns have consistent data types.

AQT will check this and display the results in the bottom grid. This can be a great time saver if you are developing a query with a large number of statements.

Statement Options tab

This tab allows you to specify a number of options relating to the type of SQL statement built.

Select Statement

This is the normal statement.

With Statement

This is discussed in WITH Statements.

Select Into

A Select Into creates a table containing the results of the query. Enter the name of the table you are creating in Table Name.

This option is only available for some databases such as SQL Server and Sybase ASE.

Create View

This will create a View from the query.

Enter the name of the View and whether (Include Col List) the column list is to be included in the View definition.

When you click on Run the view will be created. If you run it a second time you will get an error that the view already exists.

Other

This option can used for coding other typers of queries.

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.

TOP Clause

This specifies a TOP clause for SQL Server and Teradata. This returns the initial set of rows returned by the query.

Initial clause

This is a clause that can appear between the SELECT keyword and the column list.

Example for Teradata and SQL Server: TOP 50

End clause

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

Comments and Commands

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.

Clicking Disable will "remove" the command from your query. This is useful to prevent a queryparm command running every time you run the query.

Other tab

Access this from the Other tab of the Query Builder.

Use this tab to specify:

  • any parameters used by your query.

  • any comments or commands associated with the query

If you have a queryparm parameter, it will prompt the user for the value every time you run the query. This can be annoying if you are running the query on a frequent basis. To avoid being prompted, check Hide Parms.

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).

WITH Statements

AQT is able to build queries which use the WITH statement.

The use of WITH is an alternate to SELECT, and can lead to tidier SQL for complex queries. It is only available for some databases.

To specify that AQT is to build a query using WITH, on the Statement Options tab, select With statement.

Simple Example

The query:

SELECT CUSTOMER, SUM(SALE_AMOUNT) as TOT_SALES

FROM SALES_DETAILS_2015

GROUP BY by Customer

Can be written as a WITH statement as:

WITH TabA as (Select CUSTOMER, SUM(SALE_AMOUNT) as TOT_SALES from SALES_DETAILS_2015 Group by Customer)

SELECT CUSTOMER, TOT_SALES FROM TabA

In this:

  • TabA is defined as an expression. In this example, there is only one expression

  • The SELECT statement returns data from the expression(s)

Another Example

The following query displays Customer sales for two different years:

WITH

S2015 (CUSTOMER, SALES_2015) as (Select CUSTOMER, SUM(SALE_AMOUNT) from SALES_DETAILS_2015 Group by Customer),

S2016 (CUSTOMER, SALES_2016) as (Select CUSTOMER, SUM(SALE_AMOUNT) from SALES_DETAILS_2016 Group by Customer)

SELECT S2015.CUSTOMER, SALES_2015, SALES_2016

FROM S2015, S2016

WHERE S2015.Customer = S2016.Customer

In this:

  • there are two expressions. The Select statement has a Where clause to join these expressions.

  • the option Always include Column list has been specified. The expressions are preceded with Alias names for the columns.

  • tables SALES_DETAILS_2015 and SALES_DETAILS_2016 have the same columns. SUM(SALE_AMOUNT) is given a different ALIAS name in each of the expressions so it can be referenced without ambiguity in the Select clause.

Specifying the WITH Column List

In a WITH statement, you can specify a list of columns aliases for a With expression.

The column aliases can be viewed or amended on the Tables / Joins tab.

The number of columns in the WITH Column List must match the number of columns in the expression:

  • if the WITH Column List has not been specified, you can create it with Reset WITH cols. You can then click on Edit With cols to amend it with the names you require.

  • if you change the columns in your expression, you will need to change your WITH Columns to match. Reset WITH cols will recreate the WITH Columns from the expression, or use Edit WITH cols to amend as required.

User-defined relationships

Within AQT, you can define relationships between tables. This is used in:

  • the Query Builder. Once a relationship is defined between tables, the tables can be joined by simply clicking on the Add Related Tables button.

  • the Link to Related Table function of the Data Display window. This allows you to navigate between tables that have a known relationship.

In many cases, the relationships between tables will have been defined to the database as a Foreign Key relationship. In these cases, AQT will detect these relationships(*). However there are a number of circumstances where tables are related, yet this relationship is not defined to the database:

  • Your system designers have chosen not to implement the relationship (for performance or other reasons).

  • The relationship is between system tables (foreign-key relationships are not defined between these).

  • The database does not support foreign key relationships.

  • You are joining views or synonyms – foreign key relationships cannot be defined between these objects.

In these cases you can define the relationship as a User Defined Relationship, or UDR. Once you have done this, the relationship between these tables becomes a “known” relationship in the Query Builder and Data Display windows.

(*) Note that if you have set Options > Table Information > Get Related Table information from User Defined Relationships only, AQT will get the related table information from the UDRs only, and will not query the database.

To define a UDR

This can be done as follows:

  • in the Query Builder, join the tables as per normal (by dragging between columns). Once you have done this, right click the join and select Remember Join. Alternatively you can edit the join details and click on the Remember button. You will get the message “Join Details Saved”.

  • in the Query Builder, click on the Add Another Table button, then click on the Select Known Relationships tab. This will show you all the known-relationships involving the current query tables (excluding those relationships already in the query). This shows both database-defined-relationships, as well as user-defined-relationships – the Source column shows you where the relationship comes from. Click on View User Defined Relationships to go to the User Defined Relationships window from where you can add / delete / change your UDR entries.

  • in the Data Display window, click on Link > Edit list of Related Tables. This will take you to the User Defined Relationships window.

  • in the Database Explorer window, click on Tools > Edit List of Related Tables. This will take you to the User Defined Relationships window.


The User Defined Relationships window

Notes

  • Aliases are used to easily identify a relationship. You can put fuller information in the Description.

  • you may also wish to specify whether the relationship is a Foreign Key relationship. This is explained in more detail in Understanding Foreign Keys.

  • use the Where column to specify an additional Where clause for the join. This is used in cases where your join condition is (for example)

    tab1.keycol = tab2.keycol and tab1.col2 = 'X'

    in this case tab1.keycol = tab2.keycol is the join condition (as it involves both tables) and tab1.col2 = 'X' is an addition Where clause on the join.

Generic Relationships

You can specify Generic relationships. These are useful if you have the same set of tables (with the same relationships) defined in a number of different schemas. By defining the relationship as generic, you need define each relationship only once - it will then apply to all schemas.

Example: suppose you defined a generic relationship between Schema1.TableA and Schema1.TableB. This would then imply:

  • a relationship between Schema2.TableA and Schema2.TableB

  • a relationship between Schema3.TableA and Schema3.TableB

  • it does not imply relationships between schemas. So Schema1.TableA and Schema2.TableB are not related.

UDR File Location

The information on the UDRs is held in a file. There is a separate UDR file for each database. The UDR filename is database.udr (for instance aqtdemo.udr). The directory where the UDR files are stored is the Default Directory. The file name is given in the caption on the User Defined Relationships window.

You can manipulate these files in the same way as any other files. For instance, if you have a UDR file set up you can copy it to another person's PC so that they will be able to use the UDR definitions you have set up.

Similarly, if you have multiple similar databases (for instance, development and production). You can set up the UDRs for one of the databases, then copy the contents into UDR files for the other databases.

Understanding Foreign Keys

Within your database, various tables may be related to each other. The following shows a typical relationship between tables:

In this example:

  • cust_code in Order_Details gives the customer who placed the order. cust_code must match one of the values in the Customer_Details table (cust_code is the Primary Key of Customer_Details).

  • cust_code is called a Foreign Key in the Order table. This is because the values of cust_code match the key of another table.

  • within AQT, we say that Customer_Details is a Referenced Table of Order_Details.

  • Customer_Details is often called a Parent Table of Order_Details. However this can be confusing terminology, as a table can have a large number of parents, which can seem odd.

  • For each row in the Order_Details table, there will only be a single matching row in the Customer_Details table (an order can only have one customer). This relationship is therefore a many-to-one relationship.

Looking at the relationship the other way:

  • Order_Details is a Dependent Table or Child Table of Customer_Details

  • The relationship from Customer_Details to Order_Details is one-to-many (for a given customer, there can be multiple orders).

Defining a Foreign Key Relationship

Most databases allow you to define foreign key relationship. You can do this within AQT by clicking on the table then (on the Admin Toolbar), clicking on the Create (left-most) icon and selecting Foreign Key.

You can also create relationships by defining User-Defined Relationships. When you define a relationship, you need to specify whether it is a Foreign Key relationship, eg. whether the To Table is a Referenced Table or Dependent table of the From Table. To help you determine this:

  • if the To Cols are the primary key of the To Table, then it is a FK relationship.

  • If the From Cols are the primary key of the From Table then it is not a FK relationship.

  • Consider whether it is a one-to-many, or many-to-one relationship. Given one row of the From Table, do you expect there to be just one row in the To Table (many-to-one), or many rows in the To Table (one-to-many)? If it is a many-to-one relationship, then it is a FK relationship.