The ACTIVITY_COUNT status variable returns the number of rows affected by an SQL DML statement in an embedded SQL or stored procedure application. For more information check here.
As explained in its translation specification, there is a workaround to emulate ACTIVITY_COUNT's behavior through:
SELECT $1FROMTABLE(RESULT_SCAN(LAST_QUERY_ID()));
However, this presents some limitations listed below.
Limitations
First case
If ACTIVITY_COUNT is called twice or more times before executing another DML statement, the transformation might not return the expected values.
IN -> Teradata_01.sql
REPLACEPROCEDURE InsertEmployeeSalaryAndLog_1 ()BEGINDECLARE row_count1 INT;INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)VALUES (101, 'Alice', 'Smith', 10, 70000.00);-- Get the ACTIVITY_COUNTSET row_count1 = ACTIVITY_COUNT;SET row_count1 = ACTIVITY_COUNT;-- Insert the ACTIVITY_COUNT into the activity_log tableINSERT INTO activity_log (operation, row_count)VALUES ('INSERT PROCEDURE', row_count1);END;REPLACEPROCEDURE InsertEmployeeSalaryAndLog_2 ()BEGINDECLARE row_count1 INT;DECLAREmessageVARCHAR(100);INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)VALUES (101, 'Alice', 'Smith', 10, 70000.00);-- Get the ACTIVITY_COUNTSET row_count1 = ACTIVITY_COUNT +1;SET row_count1 = ACTIVITY_COUNT;-- Insert the ACTIVITY_COUNT into the activity_log tableINSERT INTO activity_log (operation, row_count)VALUES ('INSERT PROCEDURE', row_count1);END;
In both procedures, ACTIVITY_COUNT is called twice before another DML statement is called. In Teradata, ACTIVITY_COUNT will return the number of rows in the INSERT statement above them, even when called twice. However, since the Snowflake transformation uses