If you are coming from an Oracle background then you must be used to the features of the pseudo column rownum.  As already mention in this article that we do not have any such similarities present in Teradata but the same can be implemented in some or other ways. In this article we are going to discuss how to limit the output rows in BTEQ environment. To know more on how to login to BTEQ visit this link. 

Suppose we have the following command present in Oracle and we need to have the same results when we run it in Teradata.

SELECT * FROM emp WHERE rownum <=3;

In BTEQ we have a SET command to limit the number of rows of output returned to the terminal/file. It is “set retlimit <number>”.  This command has to be used with the “set retcancel” to stop query processing when the limit is reached. Create a Volatile table and insert a few rows as mentioned in this post. Logon to BTEQ session and type in the following commands one after another.

.set retlimit 3
.set retcancel on
SELECT * FROM emp ORDER BY emp_id;

ORDER BY clause mentioned in the above query is optional. Following results would be displayed on the terminal.  *** Query completed. 10 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.      emp_id  startdate  name
———–  ———  ——————————
          1   07/10/17  Bill
          2   07/10/17  Dan
          3   07/10/17  Harry
 *** Warning: RetLimit exceeded.
              Cancelling the request.

Our Random Articles

More Links