When columns in WITH clauses are referred to in the same order as they are listed in their associated SELECT clause, the return order for summary values enables BTEQ to position the summary text for all the aggregate operations in the same single result line. If the ordering does not match, BTEQ creates multiple result lines for the summary text. A separate result line is created for each aggregate operation value.
Example
The next example is a simple SELECT WITH statement in which the sequence of WITH clause column references matches the sequence of SELECT clause column references. BTEQ positions the resulting summary text within the same single result line.
SELECT
salary, edlev, hcap
FROM
employee
WITH
sum(salary), sum(edlev), sum(hcap)
WHERE
deptno = 500 ;
BTEQ Response
*** Query completed. 8 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
Salary EdLev HCap
---------- ----------- -----------
34,000.00 16 0
44,000.00 20 0
42,000.00 18 0
30,000.00 16 0
40,000.00 16 0
56,000.00 20 0
22,000.00 18 0
---------- ----------- -----------
268,000.00 124 0
All summary titles are lost because BTEQ cannot find space to fit their text into the same single result line.
Example
The next example is a simple SELECT WITH statement in which the sequence of WITH clause column references does not match the sequence of SELECT clause column references. BTEQ positions the resulting summary text within multiple result lines.
SELECT
salary, edlev, hcap
FROM
employee
WITH
sum(hcap), sum(edlev), sum(salary)
WHERE
deptno = 500 ;
BTEQ Response
*** Query completed. 8 rows found. 3 columns returned.
*** Total elapsed time was 1 second.
Salary EdLev HCap
---------- ----------- -----------
44,000.00 20 0
34,000.00 16 0
42,000.00 18 0
30,000.00 16 0
40,000.00 16 0
56,000.00 20 0
22,000.00 18 0
----------- ----------- ----------
Sum(HCap) 0
Sum(EdLev) 124
268,000.00
Summary titles have been included as part of the summary text results, with the exception of the first aggregate value. The first title remains lost due to lack of result line space.