RETCANCEL - Basic Teradata Query

Purpose  

The RETCANCEL command, used in conjunction with the RETLIMIT command, cancels a request when the value specified for rows by the RETLIMIT command is exceeded.

Syntax  

Usage Notes  

RETCANCEL Setting	With a RETLIMIT Value Specified for Rows
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.

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  

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  

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

   ECHO '.SET RETCANCEL ON';