SEPARATOR - Basic Teradata Query

SEPARATOR

Purpose  

Specifies the width and contents of the string to be placed between the columns in a report.

Syntax  

where:

Syntax Element	Specification
n	The number of blanks that separate columns. The system value is 2 by default. The maximum value is 254.
string	A character string that separates columns. The maximum length of the string is 253 characters.

Usage Notes  

The .SET SEPARATOR n form of the SEPARATOR command inserts n blanks 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  

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

To specify four blanks as the column separator, type:

   .SET SEPARATOR 4 

Example 3

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

   ECHO '.SET SEPARATOR 4';  

Example 4

To specify the ALL parameter, type:

set separator '|' all