TRAILINGLINES - 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 the number of blank lines to be printed after the Field Mode response from data-returning SQL statements.

By default, BTEQ prints extra blank lines after response from SHOW and COMMENT statements, which serve as visual delineators, separating them from a subsequent request's output. However, those extra lines could be seen as empty records when exported in Field Mode. The new command TRAILINGLINES provides a way to prevent those extra blank lines.

Syntax

where:

DEFAULT
This is the initial default. For SHOW statements, BTEQ prints one or two blank lines after the response, depending on whether the DBS returned a newline terminated response. For non-SHOW statements (such as SELECT, HELP etc), BTEQ does not print any blank lines after the response.
BETWEEN
BTEQ prints the specified number of blank lines between the set of result lines from all data-returning SQL statements. This may be used for a multi-statement request, where the last statement's response will not be followed by the extra blank lines, thus leading to only intervening blank lines.
AFTER
BTEQ prints the specified number of blank lines after the set of result lines from all data-returning statements.
n
A positive number indicating the number of blank lines to be printed, with range 0 to 2147483647.

Usage Notes

The TRAILINGLINES command is valid only for Field Mode response, and does not affect EXPORT DATA/INDICDATA.

Use the TRAILINGLINES command to configure the number of blank lines that may serve as visible delineators after the Field Mode response. The blank lines are printed only when the associated statement returns data.

When FORMAT ON is used and a FOOTING is set, which then delimits a page of formatted output with a header and footer string as per current pagelength, the TRAILINGLINES command does not have any effect.

The SET DEFAULTS command resets TRAILINGLINES to DEFAULT.

When the TRAILINGLINES command is used along with other formatting commands like SKIPLINE, SKIPDOUBLE etc, which may also produce blank lines depending on row data, users must take responsibility for data presentation format to reveal blank lines that are data as opposed to tabular result delineators.

The TRAILINGLINES command is valid in a Teradata SQL macro.

Example 1 – TRAILINGLINES AFTER 3

To print exactly three blank lines after all data returning statements, use:
.SET TRAILINGLINES AFTER 3
.EXPORT REPORT FILE = repfile1
.SET WIDTH 90
SHOW VIEW dbc.dbcinfo;
SELECT 'aaaa';
.EXPORT RESET

Output: (repfile1's content)

REPLACE VIEW DBC.DBCInfo
AS
SELECT InfoKey,
       CAST(SUBSTRING(TRANSLATE(InfoData USING UNICODE_TO_LOCALE
                      WITH ERROR) FROM 1 FOR 16384) AS VARCHAR(16384)) (NAMED InfoData) 
FROM DBC.DBCInfoTbl WITH CHECK OPTION;



'aaaa'
------
aaaa



Example 2 – TRAILINGLINES BETWEEN 3

To print exactly three blank lines between all data returning statements in a multi-statement request, use:

.SET TRAILINGLINES BETWEEN 3
.EXPORT REPORT FILE = repfile2
.SET WIDTH 90
SHOW VIEW dbc.dbcinfo
;SELECT 'aaaa';
.EXPORT RESET
Output: (repfile2's content. Notice the absence of trailing lines after the last statement.)
REPLACE VIEW DBC.DBCInfo
AS
SELECT InfoKey,
       CAST(SUBSTRING(TRANSLATE(InfoData USING UNICODE_TO_LOCALE
                      WITH ERROR) FROM 1 FOR 16384) AS VARCHAR(16384)) (NAMED InfoData)
FROM DBC.DBCInfoTbl WITH CHECK OPTION;



'aaaa'
------
aaaa

Example 3 – TRAILINGLINES AFTER 0 (In a backdoor export)

To inhibit printing extra blank lines for any data returning statements in a backdoor export, use:

.SET TRAILINGLINES AFTER 0
.EXPORT DATA FILE = repfile3
.SET RECORDMODE OFF 
.SET WIDTH 90
SHOW VIEW dbc.dbcinfo
;SELECT 'aaaa';
SHOW VIEW dbc.dbcinfo;
SELECT 'bbbb';
.EXPORT RESET

Output: (repfile3's content. Notice the absence of trailing lines after all statements.)

REPLACE VIEW DBC.DBCInfo
AS
SELECT InfoKey,
       CAST(SUBSTRING(TRANSLATE(InfoData USING UNICODE_TO_LOCALE
                      WITH ERROR) FROM 1 FOR 16384) AS VARCHAR(16384)) (NAMED InfoData)
FROM DBC.DBCInfoTbl WITH CHECK OPTION;
aaaa
REPLACE VIEW DBC.DBCInfo
AS
SELECT InfoKey,
       CAST(SUBSTRING(TRANSLATE(InfoData USING UNICODE_TO_LOCALE
                      WITH ERROR) FROM 1 FOR 16384) AS VARCHAR(16384)) (NAMED InfoData)
FROM DBC.DBCInfoTbl WITH CHECK OPTION;
bbbb