WITH Clause Output | Summary Title Accommodation | Basic Teradata Query - Summary Title Accommodation - Basic Teradata Query

Basic Teradata® Query Reference

Product
Basic Teradata Query
Release Number
17.10
Published
February 2022
Language
English (United States)
Last Update
2022-02-03
dita:mapPath
nnw1608578382132.ditamap
dita:ditavalPath
obe1474387269547.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

In the previous example, BTEQ was unable to accommodate the display of the first summary title because no room existed for it. Increasing the first column's width does not alleviate this problem. BTEQ does not place a summary title in result line space that is designated for a summary-text aggregate value. To alleviate this constraint, select empty column(s) that are aligned to make available dedicated result line space. The next example shows before and after results using this technique.

Example 1 – Summary Titles, Before

Assume a table called FruitTrees exists in the current database and a report is needed showing how many trees are ripe, plus the related yields. The table displays the following columns and values:

TreeId      Fruit            Yield  Status
----------  ----------  ----------  ----------
CITR01      ORANGE              10  NOT RIPE
CITR02      ORANGE              30  NOT RIPE
CITR03      ORANGE             500  RIPE
POME01      APPLE              100  NOT RIPE
POME02      APPLE               80  NOT RIPE
POME03      APPLE              400  RIPE
POME04      APPLE               20  RIPE

The script contains these BTEQ commands and SQL statements, which do not specify selection of any empty columns:

.SET SUPPRESS ON 1
SELECT Status
     , Fruit
     , Yield
     , TreeId
FROM   FruitTrees
GROUP BY Status, Fruit, Yield, TreeId
ORDER BY Status, Fruit, Yield
WITH     COUNT(Fruit)   (TITLE 'Trees'    )
       , SUM (Yield)    (TITLE 'Yield'    ) BY Status, Fruit
WITH     COUNT(Status)  (TITLE 'Tot Trees')
       , SUM (Yield)    (TITLE 'Tot Yield') BY Status
;

BTEQ Response

Status       Fruit              Yield  TreeId
-----------  -----------  -----------  ----------
NOT RIPE     APPLE                 80  POME02
             APPLE                100  POME01
             -----------  -----------
      Trees            2          180

             ORANGE                10  CITR01 
             ORANGE                30  CITR02
             -----------  -----------
      Trees            2           40
-----------               -----------
          4    Tot Yield          220

RIPE         APPLE                 20  POME04
             APPLE                400  POME03
             -----------  -----------
     Trees             2          420
             ORANGE               500  CITR03
             -----------  -----------
     Trees             1          500

-----------               -----------
          3    Tot Yield          920

Example 2 – Summary Titles, After

In the prior example, BTEQ finds space for only two of the four summary text titles. If the script is changed, as shown below, to include selection of empty columns that create result space for the titles, BTEQ is able to include them. Note that the selected width for the empty columns is important. The titles are truncated if the width is too small.

.SET SUPPRESS ON 1,2
SELECT   ' ' (CHAR(9)) (TITLE ''), Status
       , ' ' (CHAR(5)) (TITLE ''), Fruit
       , ' ' (CHAR(5)) (TITLE ''), Yield
                                 , TreeId
FROM     FruitTrees
GROUP BY Status, Fruit, Yield, TreeId
ORDER BY Status, Fruit, Yield
WITH     COUNT(Fruit)   (TITLE 'Trees'    )
       , SUM  (Yield)   (TITLE 'Yield'    ) BY Status, Fruit
WITH     COUNT(Status)  (TITLE 'Tot Trees')
       , SUM  (Yield)   (TITLE 'Tot Yield') BY Status
;

BTEQ Response

          Status              Fruit                     Yield     TreeId
          -----------         -----------         -----------     ----------
          NOT RIPE            APPLE                        80     POME02
                              APPLE                       100     POME01
                              -----------         -----------
                       Trees            2  Yield          180

                              ORANGE                       10     CITR01 
                              ORANGE                       30     CITR02
                              -----------         -----------
                       Trees            2  Yield           40

          -----------                             -----------
Tot Trees           4                  Tot Yield          220

          RIPE                APPLE                        20     POME04 
                              APPLE                       400     POME03
                              -----------         -----------
                       Trees            2  Yield          420

                              ORANGE                      500     CITR03
                              -----------         -----------
                       Trees            1  Yield          500
          -----------               -----------
Tot Trees           3                  Tot Yield          920