FOLDLINE - 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 whether to split each line of a report into two or more lines after the specified columns. This compresses the report into a smaller width.

Syntax



where the following is true:

n
One or more column numbers, ordered from left to right and separated by commas. The value of n can range from 1 to 2048.
Though using a comma as a separator character between column numbers is the preferred construction, a single space character is valid in place of a comma as a separator character in the BTEQ FOLDLINE command.

Usage Notes

The FOLDLINE command is valid in a Teradata SQL macro.

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

The WIDTH command specifies the maximum number of characters for each line in the report. BTEQ limits the width of a line of printed output to the WIDTH command maximum. If the returned report rows do not fit within this limit, they are truncated. To avoid this, use the FOLDLINE command to establish folding points so that BTEQ can use more than one report line to print each row. After printing the value for a column that has a FOLDLINE setting of ON, BTEQ starts a new line. If the row has remaining columns to be printed, their values are printed on the next line.

BTEQ offsets each split (i.e., folded) line belonging to a specific row, two character positions from the beginning character position for the report. After all the columns for a row have been handled, BTEQ starts forming the output lines for the next row.

For example, consider the three following sample result lines. The lines have four columns and were printed without FOLDLINE:

A1A1     B1B1     C1C1     D1D1
A2A2     B2B2     C2C2     D2D2
A3A3     B3B3     C3C3     D3D3

If FOLDLINE was set to ON for the first column, the width of the printed lines would be smaller, and the values could be printed in the following format:

A1A1
  B1B1     C1C1     D1D1
A2A2
  B2B2     C2C2     D2D2
A3A3
  B3B3     C3C3     D3D3

Although a row can be folded in several places, too much folding might produce a report that appears to be non-columnar and therefore could be confusing to read. The other factor to consider with FOLDLINE use is the way BTEQ limits the number of output lines used to print each row. In addition to instructing BTEQ to fold, rather than truncate, results data, FOLDLINE ON settings cause BTEQ to discard folded lines that are comprised solely of space characters. If a line formed to the left of the split consists solely of spaces, BTEQ does not print it.

As long as the first column for the row has its FOLDLINE setting turned on, BTEQ also discards the line formed to the right of the row's last split when that line is comprised of all spaces. Therefore, when rows have folded blank values, use FOLDLINE settings carefully to prevent potential misinterpretation of resulting reports. Other commands might be needed in combination with FOLDLINE, such as UNDERLINE or SIDETITLES, to produce the report. The UNDERLINE command displays visual row boundaries. Typically, however, the SIDETITLES command is used in conjunction with the FOLDLINE command to clearly label values so as to avoid ambiguous reports.

Using FOLDLINE with the SUPPRESS Command

Use the FOLDLINE command with the SUPPRESS command to eliminate the blank space caused by suppressing sort columns that do not change very often.

Using FOLDLINE with the PAGEBREAK Command

The FOLDLINE and PAGEBREAK commands can be used together to prevent BTEQ from splitting row values between two report pages. Set the page break column numbers so that BTEQ breaks on the first folded line for each corresponding column that has FOLDLINE set to ON. For example:

.SET PAGEBREAK ON 1; 
.SET FOLDLINE ON 1, 2, 3;

Default Values

If a FOLDLINE command is not specified, its values are OFF and ALL by default. If ON or OFF is not specified, BTEQ sets FOLDLINE to ON ALL.

To produce several reports with different FOLDLINE settings during the same BTEQ session, the FOLDLINE option must be explicitly set to OFF before specifying each new FOLDLINE command.

Example 1 – FOLDLINE

The following commands show two select operations from the tables in the Workforce sample database. The width for the report has been purposely set to 40, which is smaller than the length of the rows to be returned by the select statements. Repeating values for the first two returned columns have been suppressed to facilitate reading of the results. The first select is executed with FOLDLINE OFF for all columns. The second select is executed with FOLDLINE set to ON for column 2.

.SET WIDTH 40;
.SET SUPPRESS ON 1,2;
.SET FOLDLINE OFF;
SELECT
 e.EmpNo, e.Name,
 c.WkEnd (FORMAT 'Mmm-dd'),
 c.Hours, p.Description
FROM
 employee e, project p,
 charges c
WHERE e.EmpNo = c.EmpNo
  AND c.Proj_Id = p.Proj_Id
  AND e.DeptNo = 500
ORDER By 1,3;
.SET FOLDLINE OFF;
.SET FOLDLINE ON 2;
SELECT
 e.EmpNo, e.Name,
 c.WkEnd (FORMAT 'Mmm-dd'),
 c.Hours, p.Description
FROM
 employee e, project p,
 charges c
WHERE e.EmpNo = c.EmpNo
  AND c.Proj_Id = p.Proj_Id
  AND e.DeptNo = 500
ORDER By 1,3;

BTEQ Response

The results of the first select statement, generated with FOLDINE OFF ALL in effect, show how data rows can be truncated if they are longer than the report width. The results of the second select statement, generated with FOLDLINE ON 2 in effect, show how BTEQ folds a returned row onto multiple lines. In this case, the rows are split after printing column 2's value.

 *** Query completed. 12 rows found. 5 columns returned.
 *** Total elapsed time was 1 second.
 
                     Week
EmpNo Name 	        Ending Hours  Project
----- ------------ ------ ----- --------
10004 Smith T      Jul-29  53.0 Design W
	                   Nov-18  40.0 Design W
10010 Reed C       Feb-18  12.5 A/R RECV
                   Feb-18  10.0 A/P Paya
10014 Inglis C     Jan-14  20.0 O/E Onli
                   Jan-21  30.5 O/E Data
                   Jan-28  30.0 O/E Data
 
10015 Omura H      Feb-18  30.5 A/P Paya
                   Feb-25  24.0 A/R RECV
10016 Carter J     Jan-14  32.0 Design W
                   Feb-25   2.5 Design W
                   May-20  32.0 Design W
+---------+---------+---------+---------
                             
 *** Query completed. 12 rows found. 5 columns returned.
 *** Total elapsed time was 1 second.
EmpNo Name 
----- ------------         
    Week 
  Ending Hours   Project Description
  ------ ----- -------------------------
10004 Smith T
  Jul-29  53.0 Design Widget Pwr Supply
  Nov-18  40.0 Design Widget Frame
10010 Reed C        
  Feb-18  12.5 A/R RECV Online System
  Feb-18  10.0 A/P Payable Online System
10014 Inglis C      
  Jan-14  20.0 O/E Online System
  Jan-21  30.5 O/E Data Base Design
  Jan-28  30.0 O/E Data Base Design
10015 Omura H       
  Feb-18  30.5 A/P Payable DB Design
  Feb-25  24.0 A/R RECV Online System
10016 Carter J      
  Jan-14  32.0 Design Widget Pwr Supply
  Feb-25   2.5 Design Widget Frame
  May-20  32.0 Design Widget Pwr Supply
+---------+---------+---------+---------

Example 2 – FOLDLINE

To omit columns 1 and 2 from one report, and then, in the same BTEQ session, to omit column 3 of the next report, first specify the FOLDLINE command for the first BTEQ report:

.SET FOLDLINE 1, 2

Next, set the FOLDLINE command option to OFF for the next BTEQ report:

.SET FOLDLINE OFF

Then specify the FOLDLINE command for the current report:

.SET FOLDLINE 3

The previous FOLDLINE option must be turned off for any previously folded columns so that they appear correctly on subsequent reports.

Example 3 – FOLDLINE

The following hypothetical results show how BTEQ treats column values solely comprised of space characters when using the FOLDLINE command. The first set of response output was generated with FOLDLINE set to OFF for all columns. The second set was generated with FOLDLINE set to ON for all columns. Note how the values for the 5th row visually seem to be folded lines for the 4th row. The third set also had FOLDLINE set to ON for all columns but also used UNDERLINE ON 1 to clearly show row boundaries.

Response for FOLDLINE OFF ALL:

col1  col2  col3  col4
----  ----  ----  ----
MNO   NOP   OPQ   PQR
JKL   KLM   LMN
GHI   HIJ         IJK
DEF         EFG   FGH
      ABC   BCD   CDE

Response for FOLDLINE ON ALL:

col1
----
   col2
   ----
   col3
   ----
   col4
   ----
MNO
   NOP
   OPQ
   PQR
JKL
   KLM
   LMN
GHI
   HIJ
   IJK
DEF
   EFG
   FGH
   ABC
   BCD
   CDE

Response for FOLDLINE ON ALL with UNDERLINE ON 1:

col1
----
   col2
   ----
   col3
   ----
   col4
   ----
MNO
   NOP
   OPQ
   PQR
-------
JKL
   KLM
   LMN
-------
GHI
   HIJ
   IJK
-------
DEF
   EFG
   FGH
-------
   ABC
   BCD
   CDE