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.