SUPPRESS - Basic Teradata Query - 16.20

Basic Teradata Query Reference

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

Purpose

This control enables users to specify whether all consecutively repeated values in reports are to be replaced with space characters.

Syntax



where the following is true:

n
Column numbers are ordered from left to right and separated by commas.

The value of n can range from 1 to 2048.

Usage Notes

Use the SUPPRESS command to simplify reports by eliminating all repetitive values from the specified columns.

BTEQ always displays the first occurrence of a new value in each column, regardless of the setting of the SUPPRESS command options. After that, if the SUPPRESS command option is set to ON, BTEQ replaces those values with space characters in the specified columns. (Each row containing a new value can be preceded by one or more blank lines, depending on the settings of the SKIPLINE and SKIPDOUBLE commands options.)

If a page break occurs between suppressed values, BTEQ presents the value in the first row of the next page and then suppresses the remaining occurrences of that value.

The initial configuration of the SUPPRESS command options are OFF and ALL. If ON or OFF are not specified, or column numbers are not included, BTEQ sets SUPPRESS to ON ALL.

If several reports are produced during the same BTEQ session, and different SUPPRESS command options are used for each report, first specify .SET SUPPRESS OFF ALL before using each new SUPPRESS command.

When using the SUPPRESS and SKIPLINE commands together, the same columns specified for the SUPPRESS command are specified for the SKIPLINE command.

For example, using the following syntax:

.set suppress on 1,2,3,4,5
.set skipline on 1,2,3

BTEQ sets columns 4 and 5 for the SKIPLINE command. To specify SKIPLINE for columns 1, 2, and 3, use the following syntax:

.set suppress on 1,2,3,4,5
.set skipline off 4,5

Also, using the SHOW CONTROLS command after using the SUPPRESS command, BTEQ resets the SKIPLINE columns to the same as the SUPPRESS columns.

The SUPPRESS setting only affects the output for Field Mode, not Record Mode, Indicator Mode, or Multipart Indicator Mode.

The SUPPRESS command is valid in a Teradata SQL macro.

Example 1 – SUPPRESS

The following example returns the selected information first with the SUPPRESS command option set to OFF, then set to ON for column 1:

database workforce;
.defaults
.format on
.heading 'Suppress off'
select   deptno
,name     from     employee
order by deptno
;
.defaults
.format on
.heading 'Suppress on 1'
.suppress on 1
= 1
.format off
.logoff
.exit

BTEQ Response

*** Query completed. 21 rows found. 2 columns returned.
 Suppress off
   
DeptNo  Name
------  ------------
  100  Peterson J
  100  Chin M
  100  Greene W
  100  Moffit H
  300  Russell S
  300  Leidner P
  300  Phan A
  500  Inglis C
  500  Carter J
  500  Watson L
  500  Smith T
   .
   .
 (etc)
*** Query completed . 21 rows found. 2 columns returned.
Suppress on 1
DeptNo  Name
------  ------------
  100  Peterson J
       Chin M
        Greene W
        Moffit H
  300  Russell S
        Leidner P
        Phan A
  500  Inglis C
        Carter J
        Watson L
        Smith T
   .
   .
 (etc)
*** Query completed . 21 rows found. 2 columns returned.
Suppress on 1
DeptNo  Name
------  ------------
  100  Peterson J
       Chin M
        Greene W
        Moffit H
  300  Russell S
        Leidner P
        Phan A
  500  Inglis C
        Carter J
        Watson L
        Smith T
   .
   .
   .
 (etc)

Example 2 – SUPPRESS

To suppress columns 1 and 2 from one report, and then in the same BTEQ session, suppress column 3 of the next report, use the following SUPPRESS command for the first BTEQ report:

.SET SUPPRESS 1, 2

Then, use the following SUPPRESS commands to reconfigure the SUPPRESS command options for the next BTEQ report:

.SET SUPPRESS OFF ALL  
.SET SUPPRESS 3
First, specify .SET SUPPRESS OFF ALL before using a new SUPPRESS command.

Example 3 – SUPPRESS instigated by a macro

The SUPPRESS command in a Teradata SQL macro appears as:

ECHO '.SET SUPPRESS 1,2';