FOOTING - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose  

Specifies the footer text string that appears at the bottom of each page of a report.

Syntax  

where:

 

Syntax Element

Description

string

Text for the footer.

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

Usage Notes  

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

Using Special Characters

Do not use special characters within the string because they might be interpreted differently by different output devices. A modified script that uses special characters might be necessary if the output is routed to another device.

Using Apostrophes

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

   .SET FOOTING "December's Results"

or

   .SET FOOTING 'December''s Results' 

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

Formatting Footers

Control footing formats using two types of control characters:

 

Control Characters

Result

Double slashes (//)

Breaks the line, and begins a new line.

Solid vertical bars (||)

Separates the line into sections.

Breaking a Line

Break a footer line and begin a new line of footer text by inserting a pair of slashes
(//) at the desired break point. In BTEQ up to nine line breaks are valid (10 footer lines maximum) within a FOOTING command.

Separating a Line into Sections

Separate a footer line into as many as three sections by inserting a pair of solid vertical lines at the desired separation points. Only three sections are valid (two pairs of vertical bar separators) for each line of the footer.

 

Specification

Action

Result

A footer with no separator characters

Leaves the line as one section.

The footer is centered.

One set of separator characters

Separates the line into two sections.

The first part of the line is left-justified, and the second part of the line is centered.

Two sets of separator characters

Separates the line into three sections.

The first part of the line is left-justified, the second part of the line is centered, and the third part of the line is right-justified.

Note: Also, a pair of split vertical bars can be used as a separator character.

Exceeding the Line Separator Limit

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

 

System Type

Results

Mainframe-attached

BTEQ interprets the additional characters as footer text, and issues a warning that the limit of two pairs of vertical bar separator characters has been exceeded.

Workstation-attached

BTEQ truncates the additional separators from the footer text.

For example, in a mainframe-attached system the following command creates the warning message and incorrect footing information shown below the example command.

Command:

   .SET FOOTING '&DATE||Confidential Report||Part Two||Page&PAGE'

Warning message:

   *** Warning: Maximum of 2 '||' separators is allowed,
    others ignored. 

Incorrect footing:

   90/08/25 Confidential Report Part Two||Page2

Using Substitution Values

As shown in the preceding example, the following substitution values are valid anywhere in a footer 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. (If a footer is specified and &PAGE is not specified, the page number does not appear in the footer.)

    &n

    Is replaced with the most recent value in the nth expression of the selected row that immediately precedes the footing. The &n substitution value is not valid in the following cases:

  • When specifying .SET FOLDLINE ON m if the &n value 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 be comprised only of LATIN1 characters.

    Specifying a Footer That Is Wider than Page

    If a footer text string is wider than the page width, BTEQ first truncates the middle portion of the footer. If more must be truncated, BTEQ truncates the right and left portions until the footer fits on the page. Truncation is from the right, as shown in the following example:

       .set format on      
       .set footing 'This is an example of a footing that is too long
       for the page and is truncated starting from the right'

    A subsequent SQL SELECT statement returns the following footer:

       This is an example of a footing that is too long for
    the page and      1      

    If a Footer Is Not Specified

    If a footer is not specified, BTEQ uses the lines of the footer area at the bottom of the page for additional rows of the results from the SQL SELECT statement.

    Note: If the FORMAT command option is set to OFF, BTEQ does not process the FOOTING command when formatting output, even though the footing specifications appear in the response to a SHOW CONTROLS command. Always set the FORMAT command option to ON to include a footer in the report.

    Example

    BTEQ Commands:

       database workforce;
       .set defaults on     
       .set format on     
       .set footing 'Confidential Report' 
       select * from department 
       ;

    BTEQ Response

    In response to the Example 1 commands, BTEQ returns:

       *** Query completed. 5 rows found. 4 columns returned. 
       90/07/25      select * from department;       Page 1 
       DeptNo   DeptName          Loc   MgrNo
       ------   --------------    ---   ----- 
          500   Engineering       ATL   10012
          700   Marketing         NYC   10021
          300   Exec Office       NYC   10018
          600   Manufacturing     CHI   10007
          100   Administration    NYC   10011
                Confidential Report

    Example 2

    BTEQ commands:

       database workforce;
       .set defaults
       .set format on 
       .footing "This is an example - 
       of a continued footing- 
       with incorrect spacing" 
       select * from department
       ; 

    BTEQ Response

    In response to the Example 2 commands, BTEQ returns:

       *** Query completed. 5 rows found. 4 columns returned.
       90/07/25     select * from department;        Page 1
       DeptNo   DeptName          Loc  MgrNo
       ------   --------------    ---  ----- 
          500   Engineering       ATL  10012
          700   Marketing         NYC  10021
          300   Exec Office       NYC  10018
          600   Manufacturing     CHI  10007
          100   Administration    NYC  10011

    This is an example of a continued footing with incorrect spacing.

    Example 3

    BTEQ Commands:

       database workforce;
       .set defaults 
       .set format on     
       .footing '&date||Confidential||Page&Page' 
       select * from department 
       ; 

    BTEQ Response

    In response to the Example 3 commands, BTEQ returns:

       *** Query completed. 5 rows found. 4 columns returned.
       90/07/25    select * from department;         Page 1
       DeptNo   DeptName          Loc     MgrNo
       ------   --------------    ---     ----- 
          500   Engineering       ATL     10012
          700   Marketing         NYC     10021
          300   Exec Office       NYC     10018
          600   Manufacturing     CHI     10007
          100   Administration    NYC     10011
       
         90/07/25            Confidential            Page 1

    Example 4

    The Example 4 commands in a Teradata SQL macro appear as:

       ECHO '.SET FOOTING ''&DATE||Confidential||Page&PAGE'' ';

    or as:

       ECHO '.SET FOOTING "&DATE||Confidential||Page&PAGE"';