TRANSLATECTRLSTOSPACES - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-06-28
dita:mapPath
wmy1488824663431.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 whether BTEQ should translate the control-range characters for Field Mode columnar report's data rows (non-title) bound for stdout or an EXPORT REPORT file.

Syntax



Usage Notes

Use the TRANSLATECTRLSTOSPACES command to control the translation of control characters to spaces for Field Mode columnar report's data rows (non-title) bound for stdout or an EXPORT REPORT file.

By default, TRANSLATECTRLSTOSPACES is ON, which means BTEQ will translate certain control range characters to spaces in the following output areas of a Field Mode response.
  • BTEQ titles (headers/footers)
  • DBS titles (column titles appearing on top as well as sides)
  • Data rows (Field Mode column data)
Setting TRANSLATECTRLSTOSPACES to OFF will inhibit the translation of any control range characters to spaces in following output areas of a Field Mode response:
  • Data rows (Field Mode column data)
However, TRANSLATECTRLSTOSPACES OFF will still lead to the translation for following output areas of a Field Mode response:
  • BTEQ titles (headers/footers)
  • DBS titles (column titles appearing on top as well as sides)

An exception to the above is the TAB character, where TABs are never translated to spaces in the BTEQ titles and DBS titles.

See Field Mode Translation of Control Characters to know which control characters are considered for the translations.

The TRANSLATECTRLSTOSPACES setting only affects the output for Field Mode, not Record Mode, Indicator Mode, or Multipart Indicator Mode. In other words, TRANSLATECTRLSTOSPACES setting affects only those response lines that go to stdout or EXPORT REPORT files, and not those that go to EXPORT DATA, EXPORT INDICDATA or EXPORT DIF files.

In addition, this setting affects only the columnar response lines. For example, it affects the SELECT or HELP query output but does not affect the SHOW or COMMENT query output.

The SET DEFAULTS command will reset TRANSLATECTRLSTOSPACES setting to ON.

The TRANSLATECTRLSTOSPACES command is valid in a Teradata SQL macro.

Example 1 – TRANSLATECTRLSTOSPACES ON

The following example selects a column using TRANSLATECTRLSTOSPACES set to ON.
.SET FORMAT ON
.SET HEADING 'Titles On Top||&1||&PAGE'
.SET FOOTING 'Titles On Top||&1||&PAGE'
.SET PAGELENGTH 8
.SET TRANSLATECTRLSTOSPACES ON /* Default setting */ 
SELECT '31000102030432'xc;
.SET HEADING 'Titles On Sides||&1||&PAGE'
.SET FOOTING 'Titles On Sides||&1||&PAGE'
.SET SIDETITLES ON
.SET PAGELENGTH 6
SELECT '31000102030432'xc;

Output:

Titles On Top                     1     2                1

                                  '1    2'
                                  ––––––--
                                  1     2

Titles On Top                     1     2                1

Titles On Sides                   1     2                1

                        '1    2'  1     2

Titles On Sides                   1     2

Example 2 – TRANSLATECTRLSTOSPACES OFF

The following example selects a column using TRANSLATECTRLSTOSPACES set to OFF.
.SET FORMAT ON
.SET HEADING 'Titles On Top||&1||&PAGE'
.SET FOOTING 'Titles On Top||&1||&PAGE'
.SET PAGELENGTH 8
.SET TRANSLATECTRLSTOSPACES OFF /* No translations in data rows */
SELECT '31000102030432'xc;
.SET HEADING 'Titles On Sides||&1||&PAGE'
.SET FOOTING 'Titles On Sides||&1||&PAGE'
.SET SIDETITLES ON
.SET PAGELENGTH 6
SELECT '31000102030432'xc;

Output:

Notice that the control characters did not get translated to spaces in the column data, but still got translated in BTEQ and DBS titles.

Titles On Top                     1     2                1

                                  '1    2'
                                  ––––––--
                                  1^@^A^B^C^D2

Titles On Top                     1     2                1

Titles On Sides                   1     2                1

                        '1    2'  1^@^A^B^C^D2

Titles On Sides                   1     2                1