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