Sequence of Fields - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2020-02-20
dita:mapPath
kil1527114222313.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

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.