ReadVitamin

How To Limit Number Of Rows Output in BTEQ?

Written by admin in Wednesday, October 17th 2007 under IT

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.

Popularity: 10% [?]

Our Random Articles

4 Comments

Rastislav Said in Sunday, October 21st, 2007 @1:54 pm

would SAMPLE work the same way?
e. g. SELECT * FROM emp ORDER BY emp_id SAMPLE 3;

admin Said in Monday, October 22nd, 2007 @7:09 pm

Hi Rastislav,

The query SELECT * FROM emp ORDER BY emp_id SAMPLE 3; would NOT produce the same result as you see by using .set retlimit 3. The reason being that SAMPLE returns random sample rows, at all time. When you force a “retlimit”, it discards rest of the rows after it has selected its limit, which most of the time are TOP rows. You can use the following query to get the same result.

SELECT TOP 3 * FROM emp;

mdain Said in Wednesday, July 9th, 2008 @2:31 am

that’s not true. as long as you add the order by clause, you’d have the same results using sample

mdain Said in Wednesday, July 9th, 2008 @2:35 am

actually i take it back… you’re right. the results are different. my mistake, thanks for the idea