RETCANCEL - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2020-02-20
dita:mapPath
kil1527114222313.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose

This control enables users to specify whether to cancel a request when the number of rows specified by the RETLIMIT command is exceeded.

Syntax



where the following is true:

OFF
BTEQ stops displaying data when the specified limit is exceeded but continues gathering data from Teradata Database.

OFF is the initial default value.

It may be the best choice when executing multi-statement requests because it allows each statement to execute and process records up to the set limit.

ON
BTEQ cancels the remainder of the Teradata SQL request when the specified limit is reached.

When neither ON or OFF is specified, BTEQ sets RETCANCEL to ON.

Usage Notes

If a RETLIMIT value for rows has not been specified, the RETCANCEL command does not affect BTEQ.

The RETCANCEL command is valid in a Teradata SQL macro.

Example 1 – RETCANCEL

The following example shows the RETCANCEL command used in conjunction with the RETLIMIT command:

.set retcancel   
.set retlimit 10
select id, firstname from mytable order by 1;
   
*** Query completed. 1498 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
   
          Id  FirstName
------------  --------------------
           1  Betty
           2  Michael
           3  Rory
           4  Pat
           5  Stanley
           6  Ed
           7  Jane
           8  Edward
           9  Emily
          10  Tom
*** Warning: RetLimit exceeded.
             Cancelling the request.

Example 2 – RETCANCEL instigated by a macro

The Example 1 RETCANCEL command in a Teradata SQL macro appears as follows:

ECHO '.SET RETCANCEL ON';