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.
SELECT CUSTOMER, SUM(SALE_AMOUNT) as TOT_SALES
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
The following query displays Customer sales for two different years:
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