FORMAT - 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 to apply report layout controls for returned rows using all default and user-defined settings for the formatting commands. When enabled, the WIDTH command's setting is used to determine space available for centering the tabular report.

Syntax



For z/OS, the value of FORMAT is OFF by default for interactive mode, and ON for batch mode.

Usage Notes

The FORMAT setting determines whether or not BTEQ uses the format specifications currently set for the following page-oriented formatting commands:

  • FOOTING
  • FORMCHAR
  • HEADING
  • RTITLE
  • PAGEBREAK
  • PAGELENGTH

When the FORMAT command option is set to OFF, BTEQ does not process the configuration of these command options, even though they are shown as ON in the response to a SHOW CONTROLS command.

In general, setting FORMAT to OFF is appropriate for directing the output to the screen, and ON is appropriate for directing the output to a file for printing.

When BTEQ runs in any mode other than z/OS batch, the default is OFF because page formatting does not apply to the terminal screen when BTEQ is used online. In batch mode, the default is ON because results are printed according to page formats.

If a FORMAT command is not specified, the value is OFF by default. When the FORMAT command is used and ON or OFF is not specified, BTEQ defaults to ON.

The FORMAT command is valid in a Teradata SQL macro.

Setting the FORMAT command option to ON changes the width in batch mode for Mainframe BTEQ.

Example 1 – FORMAT

The following example shows the effects of setting the FORMAT command option to ON:

database workforce;
.set defaults
.set format on    
.set heading 'Format on'
.set sidetitles on
.set skipline on 1
.set width 40
.set pagelength 66
.footing 'Super Confidential Report'
select   MgrNo(title'')
,DeptName(title '')
,Loc(title '')
,DeptNo(title '')
from     department
order by MgrNo;

BTEQ Response

*** Query completed. 5 rows found. 4 columns returned.
Format on    
10007   Manufacturing     CHI   600
10011   Administration    NYC   100
10012   Engineering       ATL   500
10018   Exec Office       NYC   300
10021   Marketing         NYC   700
Super Confidential Report     

Example 2 – FORMAT instigated by a macro

The command in a Teradata SQL macro appears as:

ECHO '.SET FORMAT ON';

Example 3 – FORMAT

The following example shows the effects of setting the FORMAT command option to OFF:

database workforce;
.defaults
.format off
.heading 'Format off'
.set sidetitles on
.set skipline off
.set width 40
.set pagelength 66
.footing 'Super duper Confidential Report'    = 1  
;

BTEQ Response

*** Query completed. 5 rows found. 4 columns returned.     
10007   Manufacturing         CHI   600
10011   Administration        NYC   100
10012   Engineering           ATL   500
10018   Exec Office           NYC   300
10021   Marketing             NYC   700