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

This control enables users to specify the width and contents of the string to be placed between the columns in a report.

Syntax



where the following is true:

n
The number of spaces that separate columns.

The system value is 2 by default. The maximum value is 254.

string
The string that separates columns.

The system value is 2 spaces by default.

Usage Notes

The .SET SEPARATOR n form of the SEPARATOR command inserts n spaces between columns. The .SET SEPARATOR ‘string’ form of the command inserts the specified string between columns.

If a separator value prevents a report from fitting on a page, BTEQ truncates the separator on the right until the report fits on the page. BTEQ never truncates the separator to less than one character.

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

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

.SET SEPARATOR "  Item's value "

or

.SET SEPARATOR '  Item''s value ' 

If the string does not contain an apostrophe, the two forms of the SEPARATOR command are the same.

If a value for n is not specified, then n = 0.

The optional ALL parameter enables the separators to perform the following tasks:

  • Appear on ALL data lines including the summary lines associated with WITH clauses.
  • Omit the column title lines if they contain only spaces and separators.

Do not use the .SET SEPARATOR ALL command with the SIDETITLES or FOLDLINE commands. Also, when the ALL parameter is used with the summary titles appearing to the left side of the data, the summary titles might be over-written by the separators.

The SEPARATOR setting only affects the output for Field Mode, not Record Mode, Indicator Mode, or Multipart Indicator Mode.

The SEPARATOR command is valid in a Teradata SQL macro.

Example 1 – SEPARATOR

To specify a vertical bar as the column separator character, type:

 database Workforce;
.defaults
.format on
.heading "set separator |"
.set separator "|"
select Name
,EmpNo
,JobTitle
from Employee
order by Name
;
.format off
.logoff
.exit

BTEQ Response

*** Query completed. 21 rows found. 3 columns returned.  
set separator |                      
Name          |EmpNo|JobTitle
------------   ----- ------------
Aguilar J     |10007|Manager
Brangle B     |10020|Salesperson
Carter J      |10016|Engineer
Chin M        |10011|Controller
Clements D    |10022|Salesperson
Greene W      |10017|Payroll Ck
Inglis C      |10014|Tech Writer
Kemper R      |10006|Assembler
Leidner P     |10003|Secretary
Marston A     |10009|Secretary
Moffit H      |10002|Recruiter
   .
   .
   .
 (etc)

Example 2 – SEPARATOR

To specify four spaces as the column separator, type the following:

.SET SEPARATOR 4

Example 3 – SEPARATOR instigated by a macro

The prior example’s SEPARATOR command in a Teradata SQL macro appears as follows:

ECHO '.SET SEPARATOR 4'; 

Example 4 – SEPARATOR

To specify the ALL parameter, type the following:

set separator '|' all