SSC-FDM-TD0033

ACTIVITY_COUNT transformation might require manual adjustments

Description

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 $1 FROM TABLE(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.

Teradata

IN -> Teradata_01.sql

REPLACE PROCEDURE InsertEmployeeSalaryAndLog_1 ()
BEGIN
    DECLARE 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_COUNT
    SET row_count1 = ACTIVITY_COUNT;
    SET row_count1 = ACTIVITY_COUNT;

    -- Insert the ACTIVITY_COUNT into the activity_log table
    INSERT INTO activity_log (operation, row_count)
    VALUES ('INSERT PROCEDURE', row_count1);
END;

REPLACE PROCEDURE InsertEmployeeSalaryAndLog_2 ()
BEGIN
    DECLARE row_count1 INT;
    DECLARE message VARCHAR(100);

    INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
    VALUES (101, 'Alice', 'Smith', 10, 70000.00);

    -- Get the ACTIVITY_COUNT
    SET row_count1 = ACTIVITY_COUNT + 1;
    SET row_count1 = ACTIVITY_COUNT;

    -- Insert the ACTIVITY_COUNT into the activity_log table
    INSERT INTO activity_log (operation, row_count)
    VALUES ('INSERT PROCEDURE', row_count1);
END;

Snowflake

OUT -> Teradata_01.sql

CREATE OR REPLACE PROCEDURE InsertEmployeeSalaryAndLog_1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/15/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        row_count1 INT;
    BEGIN
         
        INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
        VALUES (101, 'Alice', 'Smith', 10, 70000.00);

           -- Get the ACTIVITY_COUNT
        row_count1 := (
            SELECT
                $1
            FROM
                TABLE(RESULT_SCAN(LAST_QUERY_ID()))
        ) /*** SSC-FDM-TD0033 - 'ACTIVITY_COUNT' TRANSFORMATION MIGHT REQUIRE MANUAL ADJUSTMENTS ***/;
        row_count1 := (
            SELECT
                $1
            FROM
                TABLE(RESULT_SCAN(LAST_QUERY_ID()))
        ) /*** SSC-FDM-TD0033 - 'ACTIVITY_COUNT' TRANSFORMATION MIGHT REQUIRE MANUAL ADJUSTMENTS ***/;

        -- Insert the ACTIVITY_COUNT into the activity_log table
        INSERT INTO activity_log (operation, row_count)
        VALUES ('INSERT PROCEDURE', :row_count1);
    END;
$$;

CREATE OR REPLACE PROCEDURE InsertEmployeeSalaryAndLog_2 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "07/15/2024" }}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        row_count1 INT;
        message VARCHAR(100);
    BEGIN
         
         
        INSERT INTO employees (employee_id, first_name, last_name, department_id, salary)
        VALUES (101, 'Alice', 'Smith', 10, 70000.00);

           -- Get the ACTIVITY_COUNT
        row_count1 := (
            SELECT
                $1
            FROM
                TABLE(RESULT_SCAN(LAST_QUERY_ID()))
        ) /*** SSC-FDM-TD0033 - 'ACTIVITY_COUNT' TRANSFORMATION MIGHT REQUIRE MANUAL ADJUSTMENTS ***/ + 1;
        row_count1 := (
            SELECT
                $1
            FROM
                TABLE(RESULT_SCAN(LAST_QUERY_ID()))
        ) /*** SSC-FDM-TD0033 - 'ACTIVITY_COUNT' TRANSFORMATION MIGHT REQUIRE MANUAL ADJUSTMENTS ***/;

        -- Insert the ACTIVITY_COUNT into the activity_log table
        INSERT 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