OMIT

Basic Teradata Query Reference

brand
Teradata Tools and Utilities
prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

Purpose

This control enables users to specify whether any columns are to be excluded from Field Mode reports for subsequent data-returning SQL statements.

Syntax



where the following is true:

n
Specifies the number of columns to omit, ordered from left to right, and separated by a comma or one space.

The value of n can range from 1 to 2048.

Usage Notes

The OMIT command is useful to underscore a field or to cause a pagebreak at a field not to be displayed.

The initial values of the OMIT command are OFF and ALL by default. If ON or OFF is not selected, BTEQ sets OMIT to ON ALL.

To produce several reports during the same BTEQ session, and change the OMIT specifications for each report, specify .SET OMIT OFF before supplying each new OMIT command specification.

Turn off any prior OMIT command specifications to display previously omitted columns on subsequent reports.

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

The OMIT command can be used in a Teradata SQL macro.

Example 1 – OMIT

The following example shows the results of using the OMIT command specifications three ways:

  • With the default configuration
  • With all columns omitted
  • With one column (i.e., column 2) omitted
database workforce;
.set defaults
.set format on
.heading "Example 1"
.set omit off
select deptno
,Loc(Title 'Department Location')
from department
order by deptno
;
.set format on
.set defaults
.set omit off
.set omit on all
.heading 'Example 2'
= 1
.set defaults
.set format on
.set omit off
.set omit on 2
.heading 'Example 3'
= 1
.defaults
.set format off
.logoff
.exit

BTEQ Responses

In the first select operation, with the OMIT command specifications set to the default configuration, BTEQ returns the following response:

*** Query completed. 5 rows found. 2 columns returned.
                            Example 1
DeptNo   Department Location
------   -------------------
   100   NYC
   300   NYC
   500   ATL
   600   CHI
   700   NYC

In the second select operation, with the OMIT command specifications set to ON ALL, BTEQ returns the following response:

*** Query completed. 5 rows found. 2 columns returned.
                           Example 2

In the third select operation, with the OMIT command specifications set to omit column two, BTEQ returns the following response:

*** Query completed. 5 rows found. 2 columns returned.
                           Example 2

In the third select operation, with the OMIT command specifications set to omit column two, BTEQ returns the following response:

*** Query completed. 5 rows found. 2 columns returned.
                           Example 3
DeptNo
------
   100
   300
   500
   600
   700

Example 2 – OMIT instigated by a macro

The Example 1 OMIT command in a Teradata SQL macro appears as:

ECHO '.SET OMIT ON 2';