Limiting the Amount of Display Data - Basic Teradata Query

Basic Teradata Query Reference

Basic Teradata Query
Release Number
May 2017
English (United States)
Last Update
Product Category
Teradata Tools and Utilities

When using either Record or Indicator Mode in an interactive session, it might be necessary to limit the number of rows of data displayed on the terminal screen. For example, while developing the format portion of a BTEQ script or macro, it might not be necessary to see all of the data. Only seeing a few rows to evaluate the results of the format statements are needed.

Use the following BTEQ commands to limit the number of rows of data in the screen display:

Comment Function
RETCANCEL Use this command to cancel the request after displaying the specified number of rows.
RETLIMIT Use RETLIMIT’s ROWS option to specify the number of rows of return data to display on the terminal screen.
  • If a return limit value is specified for rows, but the RETCANCEL control is set to OFF, BTEQ stops displaying data at the specified limit, but continues processing the request.
  • The value of the RETLIMIT setting ROWS option is zero by default, which is no limit. If RETCANCEL is set to ON without specifying a return limit for rows, BTEQ still retrieves and displays all request data.
  • When BTEQ receives the response in Record Mode or Indicator Mode and determines that the standard output stream has been specified instead of an export file, BTEQ represents the data as an octal or hexadecimal dump with side-by-side EBCDIC translation. One dump is generated for each row resulting from a data-returning SQL statement .

Example – Amount of Display Data

The display is limited to two rows of Record Mode data in the example below:

.Set retlimit 2
.Set recordmode on  
select * from fml.department;

Example display:

*** Success , Stmt#1, ActivityCount = 5
*** Time was 4.70 seconds.
*** Record#1. Dump Of Data:
 0000  F401 000B 456E 6769  6E65 6572 696E 6741  *....EngineeringA*
 0010  544C 1C27                                 *TL.'*
*** Record#2. Dump Of Data:
 0000  BC02 0009 4D61 726B  6574 696E 674E 5943  *....MarketingNYC*
 0010  2527                                      *%'* 
[*** Warning: RetLimit exceeded.
              Ignoring the rest of the output.

Similarly, the display is limited to two rows of Indicator Mode data in the example below:

.Set retlimit 2
.Set indicatormode on  
select * from fml.department; 

Example display:

*** Success , Stmt#1, ActivityCount = 5
*** Time was 0.51 second. 
*** Dump of Parcel DATAINFO:  
 0000  0400 F401 0200 C101  0E00 C501 0#00 F401  *................*
 0010  0200                                   *..*
*** Record#1. Dump Of Data:
 0000  00F4 0100 0B45 6E67  696E 6565 7269 6E67  *.....Engineering*
 0010  4154 4C1C 27                           *ATL.'* 
*** Record#2. Dump Of Data:
 0000  00BC 0200 094D 6172  6B65 7469 6E67 4E59  *.....MarketingNY*
 0010  4325 27                                *C..* 

To return to normal Field Mode, set either the RECORDMODE or the INDICATORMODE command option to OFF, and reset the RETLIMIT command ROWS option to zero, as shown in the following example:




followed by: