Purpose
This control enables users to specify the maximum number of rows and columns displayed on the terminal or written to a file in response to an SQL statement.
Syntax
| Command | Shorthand |
|---|---|
| RETLIMIT | RL |
where the following is true:
- n1
- Maximum number of rows to be displayed at a terminal or written to a file.
To specify a limit, the value of n1 must be greater than 0 and less than 18,446,744,073,709,551,616.
To disable the limit function, set n1 to an asterisk (*) or zero. This will cause BTEQ to return all rows selected.
The value is 0 by default.
- n2
- Maximum number of columns to be displayed at a terminal or written to a file. This element is optional.A value must be entered for n1 before entering a value for n2.
The value of n2 can be an asterisk or a number between 1 and 65535. However, being logged on to a database is required if the number entered is greater than 2048. An asterisk (*) signifies 2048 number of returnable columns. This value is subject to change to support future Teradata product enhancements. To fix the limit of columns that are displayed, enter a number instead of an asterisk.
For the current column limit, see "Teradata System Limits" in Teradata Vantage™ - Database Administration, B035-1093.
The value is 100 by default.
Usage Notes
The database always returns all rows that comprise the full result set for an SQL statement. By default, BTEQ uses a maximum of 100 columns to generate its results. If more than 100 columns are necessary for the results. Use the RETLIMIT command to increase the limit for the number of columns retained.
The RETLIMIT command applies to each SQL statement, not to SQL requests. Therefore, if an SQL request contains several SQL statements, BTEQ processes the specified number of rows for each statement. When the return limit for a statement is exceeded, BTEQ returns the following messages:
*** Warning: RetLimit exceeded. Ignoring the rest of the output.
The DEFAULTS command resets RETLIMIT setting to its initial default.
The RETLIMIT command is valid in an SQL macro.
Before increasing the column limit, it might be necessary to reestablish other settings so that the effect of the setting is populated over the additional columns. For example, to omit all but the third column, use the OMIT ON ALL command before changing the column limit. After changing the column limit, use the OMIT ON 3 command. Otherwise, the ON attribute is not populated for the additional columns.
Example 1 – RETLIMIT
The database limit for the maximum number of columns that can be selected sometimes changes. If it is unimportant that this limit can change, and the maximum number of rows and columns needs to be used for result output, use:
.SET RETLIMIT * *;
Example 2 – RETLIMIT
This example specifies the maximum number of rows and columns that should be used for result output when the column retention limit must remain the same, without regard to whether the database limit has been increased:
.SET RETLIMIT * 2048;
Example 3 – RETLIMIT
This example shows a series of SQL SELECT statements used first without a RETLIMIT command (Retlimit Off heading), and then repeated twice with RETLIMIT values of 1 (Retlimit 1 heading), and 4 (Retlimit 4 heading):
database workforce; .defaults .format on .heading 'Retlimit off' select name ,deptno from employee order by deptno; .defaults .format on .heading "Set Retlimit 1" .set retlimit 1 =1 .defaults .format on .heading "Set Retlimit 4" .set retlimit 4 =1 .format off .quit
BTEQ Response
*** Query completed. 21 rows found. 2 columns returned. Retlimit off Name DeptNo ------------ ------ Peterson J 100 Chin M 100 Greene W 100 Moffit H 100 Russell S 300 Leidner P 300 Phan A 300 Inglis C 500 Carter J 500 Watson L 500 Smith T 500 Omura H 500 Reed C 500 . . (etc) *** Query completed. 21 rows found. 2 columns returned. Set Retlimit 1 Name DeptNo ------------ ------ Peterson J 100 *** Warning: RetLimit exceeded. Ignoring the rest of the output. *** Query completed. 21 rows found. 2 columns returned. Set Retlimit 4 Name DeptNo ------------ ------ Peterson J 100 Chin M 100 Greene W 100 Moffit H 100 *** Warning: RetLimit exceeded. Ignoring the rest of the output.
Example 4 – RETLIMIT Instigated by a Macro
The RETLIMIT command in an SQL macro appears as follows:
ECHO '.SET RETLIMIT 4';
Example 5 – RETLIMIT
To disable the current retention limit for rows, use the following:
.SET RETLIMIT 0