Sequence of Fields - Basic Teradata Query - 16.20

Basic Teradata Query Reference

prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

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 – Sequence Matches

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 – Sequence Does Not Match

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.