HEADING and RTITLE - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2020-02-20
dita:mapPath
kil1527114222313.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose

HEADING – This control enables users to specify a header that appears at the top of each page of a report.

RTITLE – This control enables users to specify a title that appears at the top of each page of a report. Titles specified by the RTITLE command automatically include the date and page number.

HEADING Syntax



where:
string
Text for the header.

The string can contain a maximum of 253 characters, including line separator characters, to describe up to 10 lines.

RTITLE Syntax



where the following is true:
string
Text for the title.

The text string can contain a maximum of 253 characters, including line separator characters, to describe up to 10 lines.

Usage Notes

The HEADING command and the RTITLE command are alternate versions of the same command. They both specify the top ten lines on each page of a report. If using both commands, the most recent one overrides the earlier one.

RTITLE command use results in automatic inclusion of page numbers and the date in page headers; HEADING command use does not. When using the HEADING command, &PAGE and &DATE substitution values must be included as part of the command argument string to get page numbers and the date included in page headers.

Do not use special characters within the header string as they might be interpreted differently by different output devices. It might be necessary to modify a script that uses special characters if it is necessary to route the output to another device.

If the string has an apostrophe (single quote) character, use the second form of the command (the one with double quotes as delimiters) or double the apostrophe. For example:

.SET HEADING "December's Results"

or

.SET RTITLE 'December''s Results' 

If the string does not have an apostrophe, the two forms of the commands are equivalent.

All of the subsequent sections apply to both the HEADING and RTITLE commands.

Formatting Headings

Control heading formats using two types of control characters:

Control Characters Description
Double slashes (//) Breaks the line, and begins a new line.
Solid vertical bars (||) Separates the line into sections.

Breaking a Line

To break a header line and begin a new line of header text, insert a pair of slashes (//) at the designated break point. BTEQ creates up to nine line breaks (10 header lines maximum) within a HEADING command.

Separating a Line into Sections

To separate a header line into as many as three sections, insert a pair of solid vertical lines at the designated separation points. BTEQ creates only three sections (two pairs of vertical bar separators) for each line of the header.

Specification Action Results
A header with no separator characters The header line is unchanged. The header is centered.
One set of separator characters The line is separated into two sections. The left section of the line is left-justified, and the right section of the line is centered.
Two sets of separator characters The line is separated into three sections. The left-most section of the line is left-justified, the middle section of the line is centered, and the right-most section of the line is right-justified.
Also, a pair of split vertical bars can be a separator character.

If more than two pairs of vertical bar separator characters are specified, BTEQ does the following:

System Type Result
Mainframe-attached BTEQ interprets the additional characters as header text and issues a warning that the limit of two pairs of vertical bar separator characters has been exceeded.
Workstation-attached BTEQ truncates any additional text including the extra (||) or 
(//) from the header.

Using Substitution Values

Use the following substitution values anywhere in a header string:

  • &DATE
  • &TIME
  • &PAGE
  • &n

where:

Substitution Value Results
&DATE Is replaced with the current date in YY/MM/DD format.
&TIME Is replaced with the current time in HH:MM format.
&PAGE Is replaced with the current page number.
&n Is replaced with the value in the nth expression of the returned row that immediately follows the header.

The &n substitution value can be used in the following cases:

  • when specifying .SET FOLDLINE ON m.
  • when &n is greater than the value of m, and &n is greater than one
  • when specifying .SET FOLDLINE ON ALL.

For non-exported output within z/OS BTEQ Unicode sessions, this value must comprise only LATIN1 characters.

Blank Header/Title Lines

Entering a HEADER/RTITLE command with one or more spaces as the title string (for example .SET RTITLE' ' or .SET RTITLE" "), causes BTEQ to generate a blank title.

Two Apostrophes

Using a HEADING/RTITLE command with two apostrophes or quotation marks with no spaces between them (for example .SET RTITLE '' or .SET RTITLE ""), causes BTEQ to use as much of the SQL request text that will fit for the title string.

Default Headings and Titles

If the RTITLE command is not used to specify a title, or the HEADING command to specify a header, the default one-line title or header includes the date, as much of the SQL request text that will fit, and the page number, formatted as follows:

&DATE||as much SQL request text as can fit here||Page&PAGE

Specifying a Header That is Wider Than the Page

If specifying a header string that is wider than the page, BTEQ truncates from the right until the header fits on the page. (A header can be continued from one line to the next by including a dash character (-) as the last nonspace character of the line to be continued.)

If the HEADING command is not used to specify a heading, or the RTITLE command to specify a title, the default one-line header or title includes the date, as much of the SQL request text that fits, and the page number, in the following format :

&DATE||as much SQL request text as can fit here||Page&PAGE"
If the FORMAT command option is set to OFF, BTEQ does not process the HEADING command when formatting output, even though the heading specifications appear in the response to a SHOW CONTROLS command. Always set the FORMAT command option to ON to display a header in the reports.

When using the HEADING command with the FOLDLINE command, the heading can be shown on the first page.

The HEADING and RTITLE commands are valid in a Teradata SQL macro.

Example 1 – HEADING

Omitting all data from a report does not suppress generation of the page headers. In the following examples, OMIT is being used for brevity of results.

database workforce;
.set defaults on
.set format on
.set heading 'Confidential Report'
.omit on
select * from employee
;

BTEQ Response

*** Query completed. 21 rows found. 12 columns returned.
          Confidential Report

Example 2 – HEADING

Specifying the argument string for the HEADING command using input line continuation.

database workforce;
.set defaults
.set format on
.set heading 'This is an example -
of a continued -
heading'  
.omit on  
select * from employee
;

BTEQ Response

*** Query completed. 21 rows found. 12 columns returned.
This is an example of a continued heading

Example 3 – HEADING

Using a multi-line heading that is also sectioned.

database workforce;
.set defaults 
.set format on 
.heading '&date||Marketing||Page&Page//Third Quarter//Chicago'
.omit on  select * from employee 
;
.set format off
.logoff
.exit

BTEQ Response

*** Query completed. 21 rows found. 12 columns returned.
   90/07/24              Marketing                 Page 1
                       Third Quarter
                       Chicago

Example 4 – HEADING instigated by a macro

The HEADING command in a Teradata SQL macro appears as follows:

ECHO '.SET HEADING
''&date||Marketing||Page&Page//Third -
Quarter//Chicago'' ';

or as:

ECHO '.SET HEADING
&date||Marketing||Page&Page// Third -
Quarter//Chicago"';

Example 5 – RTITLE

The following example uses Rtitle On as the title string:

database workforce;
.set defaults on
.set format on
.set rtitle 'Rtitle On'
select * from department;
.set defaults
.set format off
.logoff
.exit

BTEQ Response

*** Query completed. 5 rows found. 4 columns returned.   
90/08/01                Rtitle On                  Page 1
DeptNo  DeptName         Loc  MgrNo
------  --------------   ---  -----
   500  Engineering      ATL  10012
   700  Marketing        NYC  10021
   300  Exec Office      NYC  10018
   600  Manufacturing    CHI  10007
   10 0  Administration   NYC  10011