ASA SQL User's Guide
Common Table Expressions
In general, common table expressions are useful whenever a table expression must appear multiple times within a single query. The following typical situations are suited to common table expressions.
Queries that involve multiple aggregate functions.
Views within a procedure that must contain a reference to a program variable.
Queries that use temporary views to store a set of values.
This list is not exhaustive. You may encounter many other situations in which common table expressions are useful.
Common table expressions are useful whenever multiple levels of aggregation must appear within a single query. This is the case in the example used in the previous section. The task was to retrieve the department ID of the department that has the most employees. To do so, the count aggregate function is used to calculate the number of employees in each department and the max function is used to select the largest department.
A similar situation arises when writing a query to determine which department has the largest payroll. The sum aggregate function is used to calculate each department's payroll and the max function to determine which is largest. The presence of both functions in the query is a clue that a common table expression may be helpful.
WITH DeptPayroll( dept_id, amt ) AS
( SELECT dept_id, sum(salary) AS amt
FROM employee GROUP BY dept_id )
SELECT dept_id, amt
FROM DeptPayroll
WHERE amt = ( SELECT max(amt)
FROM DeptPayroll )Sometimes, it can be convenient to create a view that contains a reference to a program variable. For example, you may define a variable within a procedure that identifies a particular customer. You want to query the customer's purchase history, and as you will be accessing similar information multiple times or perhaps using multiple aggregate functions, you want to create a view that contains information about that specific customer.
You cannot create a view that references a program variable because there is no way to limit the scope of a view to that of your procedure. Once created, a view can be used by in other contexts. You can, however, use a common table expressions within the queries in your procedure. As the scope of a common table expression is limited to the statement, the variable reference creates no ambiguity and is thus permitted.
The following statement selects the gross sales of the various sales representatives in the sample database.
SELECT emp_fname || ' ' || emp_lname AS sales_rep_name,
sales_rep AS sales_rep_id,
sum( p.unit_price * i.quantity ) AS total_sales
FROM employee LEFT OUTER JOIN sales_order AS o
INNER JOIN sales_order_items AS i
INNER JOIN product AS p
WHERE '2000-01-01' <= order_date
AND order_date < '2001-01-01'
GROUP BY sales_rep, emp_fname, emp_lnameThe above query is the basis of the common table expression that appears in the following procedure. The ID number of the sales representative and the year in question are incoming parameters. As this procedure demonstrates, the procedure parameters and any declared local variables can be referenced within the WITH clause.
CREATE PROCEDURE sales_rep_total (
IN rep INTEGER,
IN yyyy INTEGER )
BEGIN
DECLARE start_date DATE;
DECLARE end_date DATE;
SET start_date = YMD( yyyy, 1, 1 );
SET end_date = YMD( yyyy, 12, 31 );
WITH total_sales_by_rep ( sales_rep_name,
sales_rep_id,
month,
order_year,
total_sales ) AS
( SELECT emp_fname || ' ' || emp_lname AS sales_rep_name,
sales_rep AS sales_rep_id, month( order_date),
year(order_date),
sum( p.unit_price * i.quantity ) AS total_sales
FROM employee LEFT OUTER JOIN sales_order o
INNER JOIN sales_order_items i
INNER JOIN product p
WHERE start_date <= order_date AND
order_date <= end_date AND
sales_rep = rep
GROUP BY year(order_date), month(order_date),
emp_fname, emp_lname, sales_rep )
SELECT sales_rep_name,
monthname( YMD(yyyy, month, 1) ) AS month_name,
order_year,
total_sales
FROM total_sales_by_rep
WHERE total_sales =
( SELECT max( total_sales) FROM total_sales_by_rep )
ORDER BY order_year ASC, month ASC;
END;The following statement demonstrates how to call the above procedure.
CALL sales_rep_total(129, 2000);
Sometimes, it can be useful to store a particular set of values within a SELECT statement or within a procedure. For example, suppose a company prefers to analyze the results of its sales staff by thirds of a year, instead of by quarter. Since there is no built-in date part for thirds, as there is for quarters, it is necessary to store the dates within the procedure.
WITH thirds (q_name, q_start, q_end) AS
( SELECT 'T1', '2000-01-01', '2000-04-30' UNION
SELECT 'T2', '2000-05-01', '2000-08-31' UNION
SELECT 'T3', '2000-09-01', '2000-12-31' )
SELECT q_name,
sales_rep,
count(*) AS num_orders,
sum( p.unit_price * i.quantity ) AS total_sales
FROM thirds LEFT OUTER JOIN sales_order AS o
ON q_start <= order_date AND order_date <= q_end
INNER JOIN sales_order_items AS i
INNER JOIN product AS p
GROUP BY q_name, sales_rep
ORDER BY q_name, sales_repThis method should be used with care, as the values may need periodic maintenance. For example, the above statement must be modified if it is to be used for any other year.
You can also apply this technique within procedures. The following example declares a procedure that takes the year in question as an argument.
CREATE PROCEDURE sales_by_third ( IN y INTEGER )
BEGIN
WITH thirds (q_name, q_start, q_end) AS
( SELECT 'T1', YMD( y, 01, 01), YMD( y, 04, 30) UNION
SELECT 'T2', YMD( y, 05, 01), YMD( y, 08, 31) UNION
SELECT 'T3', YMD( y, 09, 01), YMD( y, 12, 31) )
SELECT q_name,
sales_rep,
count(*) AS num_orders,
sum(p.unit_price * i.quantity) AS total_sales
FROM thirds JOIN sales_order AS o
ON q_start <= order_date AND order_date <= q_end
KEY JOIN sales_order_items AS i
KEY JOIN product AS p
GROUP BY q_name, sales_rep
ORDER BY q_name, sales_rep;
END;CALL sales_by_third (2000);