Conditional execution of SQL statements using activity count in Teradata BTEQ
Contents
ACTIVITY_COUNT in Teradata
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
|
1 |
.IF ACTIVITYCOUNT = 0 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
.logon v_logonString; SELECT * FROM Banking_DB.Customers where customer_id = 458933; .IF ACTIVITYCOUNT = 0 THEN .GOTO INSERT_RECORD .EXIT 1 .LABEL INSERT_RECORD INSERT INTO Banking_DB.Customers VALUES ( 458933, 'User1', 'Savings Account', 'Active', '$400' ); |
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
- Insert Into Statement in Teradata
- INSERT INTO SELECT Statement in Teradata with examples
- How to run the BTEQ script in Teradata using Unix shell script
