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
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