RETLIMIT
Purpose
Specifies the maximum number of rows and columns displayed on the terminal or written to a file in response to a Teradata SQL statement.
Syntax
where:
|
Syntax Element |
Description |
|
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. Note: 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 DBS 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 releases of Teradata Database. To fix the limit of columns that are displayed, enter a number instead of an asterisk. For the current Teradata Database limit, refer to SQL Fundamentals, Appendix C. The value is 100 by default. |
Usage Notes
Teradata Database always returns all of the rows that are selected by a Teradata SQL SELECT statement. By default, however, 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 Teradata SQL statement, not to Teradata SQL requests. Therefore, if a Teradata SQL request contains several Teradata 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.
Note that using Teradata SQL’s TOP operator might be a better choice than the RETLIMIT command when a multi-statement request is comprised of multiple data-returning statements, only one of which is to be constrained. That one constrained statement employs the TOP operator. When rows are specifically ordered (for example, by an ORDER BY clause), it is the first n rows that TOP ensures are returned. When the selection is not ordered, TOP returns any n rows.
The RETLIMIT command is valid in a Teradata 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
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
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
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
The Example 3 RETLIMIT command in a Teradata SQL macro appears as:
ECHO '.SET RETLIMIT 4';
Example 5
To disable the current retention limit for rows, use:
.SET RETLIMIT 0
Note: Setting the RETLIMIT row value to 0 returns BTEQ to the unlimited return mode.