Conditional execution of SQL statements using activity count in Teradata BTEQ


Activity count is a variable that returns the number of rows affected by the SQL statement in Teradata. It is initialized to zero when SQL application begins execution and is updated during run time after each executable SQL statements processed.

Syntax to use Activity count

In Teradata Bteq script, we can validate the activity count after each SQL execution to decide the next action. Either we can use ACTIVITYCOUNT = 0 or ACTIVITYCOUNT <> 0 in the Bteq script.

Example BTEQ with Activity Count

Here we are trying to insert the new customer into the Customers table. If the customer has already exist in the table, we will exit the Bteq script using Activity count variable.

The select query will return the number of rows if the given customer id present in the Customers table. Also the count of rows will store it in the activity count.

GOTO and LABEL command in BTEQ

Then we can validate the activity count using IF condition. If the activity count is zero, Insert query will be executed using .GOTO command .The insert query is labeled as INSERT_RECORD using .LABEL command.

If the activity count is non zero, it will exit the BTEQ script and the customer will not be inserted into the Customers table.

Recommended Articles