Purpose
This control enables users to specify whether BTEQ should translate the control-range characters for Field Mode report's data rows and title rows (header/footer or DBS titles) bound for stdout or an EXPORT REPORT file.
Syntax
| Command | Shorthand |
|---|---|
| TRANSLATECTRLSTOSPACES | TCTS |
Usage Notes
Use the TRANSLATECTRLSTOSPACES command to control the translation of control characters to spaces for Field Mode report's data rows and title rows bound for stdout or an EXPORT REPORT file.
- BTEQ titles (headers/footers)
- Database titles (column titles appearing on top as well as sides)
- Data rows (Field Mode column data)
- Data rows (Field Mode column data)
- BTEQ titles (headers/footers)
- Database titles (column titles appearing on top as well as sides)
Here the TAB character is an exception, where TABs are never translated to spaces in the BTEQ titles and database titles.
Setting TRANSLATECTRLSTOSPACES to OFF ALL will inhibit the translation for both data rows and as well as BTEQ and database titles.
Here the NULL character is an exception, where NULLs are always translated to spaces in the BTEQ titles and database titles.
To know which more about the control characters are considered for the translations, see Field Mode Translation of Control Characters.
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 DEFAULTS command resets TRANSLATECTRLSTOSPACES setting to ON.
The TRANSLATECTRLSTOSPACES command is valid in an 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 database 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
Example 3 - TRANSLATECTRLSTOSPACES OFF ALL
The following example selects a column using TRANSLATECTRLSTOSPACES set to OFF ALL.
.SET FORMAT ON .SET HEADING 'Titles On Top||&1||&PAGE' .SET FOOTING 'Titles On Top||&1||&PAGE' .SET PAGELENGTH 8 .SET TRANSLATECTRLSTOSPACES OFF ALL /* No translations in data and header/footer/title 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 as well as BTEQ and database titles.
Notice that the NULL character still gets translated to space in BTEQ and database titles but not in the data row.
Titles On Top 1 ^A^B^C^D2 1
'1 ^A^B^C^D2'
---------
1^@^A^B^C^D2
Titles On Top 1 ^A^B^C^D2 1
Titles On Sides 1 ^A^B^C^D2 1
'1 ^A^B^C^D2' 1^@^A^B^C^D2
Titles On Sides 1 ^A^B^C^D2 1