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.
where the following is true:
- 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.
The FOLDLINE command is valid in an 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;
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;
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