Snowflake procedure looping issue with value being incremented implicitly

I am working on a procedure in snowflake (standard edition) that parses query_text from "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" to identify any tables that are accessed. Since we're on standard edition, we don't have access to the ACCESS_HISTORY table, which appears to only be populated in enterprise edition.

I'm using regexp_cnt to identify how many instances of from's and join's exist in the query text and then attempting to loop through each instance to capture the table it hits. Somehow, it is incrementing my variable that I am setting with the regexp_cnt along with the counter that I am setting for looping. I'm not incrementing the regexp_cnt variables anywhere.

Procedure is a bit lengthy so I will supply the key components here:

This is where I'm setting my from and join count values

// get counts of from and join clauses
    var get_query_history_loops_sql = `create or replace temporary table tmp_query_hist_clean_counts as select query_id, user_name, start_time, query_text, clean_query_text, regexp_count(clean_query_text, 'from ', 1, 'i') as f_count, regexp_count(clean_query_text, 'join ', 1, 'i') as j_count from tmp_query_hist_clean where f_count > 0;`;

This query has two instances each of from and join in the query_text.

Results:

The resulting counts are being set to variables v_from_count and v_join_count

This is the section where I'm trying to loop through the instances to pick up the resulting table

// loop through from counts and join counts to retrieve table information 
    var get_from_join_cnt_sql = `select query_id, user_name, start_time, query_text, clean_query_text, f_count, j_count from tmp_query_hist_clean_counts order by query_id;`

    try{
        processes=processes+"call get_from_join_cnt_sql to select query information to loop through for from cnt and join cnt / "
        stmt = snowflake.createStatement(
        {sqlText: get_from_join_cnt_sql}); 
        from_join_cnt_result = stmt.execute();
        
        var while_counter=0;
        while (from_join_cnt_result.next()){
            v_from_count = from_join_cnt_result.getColumnValue('F_COUNT');
            v_join_count = from_join_cnt_result.getColumnValue('J_COUNT');
            v_q_id = from_join_cnt_result.getColumnValue('QUERY_ID');

            if (v_from_count !== 0) {
                v_from_counter = 1;
//              for (let v_from_counter=1; v_from_counter < v_from_count+1; v_from_counter++) {
                while (v_from_counter < v_from_count+1) {
                get_from_loop_table_name_values_sql=`insert into tmp_tbl_query_hist_loops (query_id, user_name, start_time, query_text, clean_query_text, table_name, table_name_split_0, table_name_split_1, table_name_split_2, table_name_split_3, qualified_table_name, table_catalog, table_schema, from_count, from_counter, join_count, join_counter) 

                select c.query_id, c.user_name, c.start_time, c.query_text, c.clean_query_text, c.table_name, c.table_name_split_0, c.table_name_split_1, c.table_name_split_2, c.table_name_split_3, c.qualified_table_name, c.table_catalog, c.table_schema, `+v_from_count+` as from_count, c.from_counter, `+v_join_count+` as join_count, c.join_counter
                from 
                    (select b.query_id, b.user_name, b.start_time, b.query_text, b.clean_query_text, b.table_name, b.from_counter, b.join_counter
                    , b.table_name_split_0, b.table_name_split_1, b.table_name_split_2, b.table_name_split_3
                    , case when b.table_name_split_3 <> '' then b.table_name_split_3 
                            when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 = '' 
                            then b.table_name_split_2 
                            else b.table_name end as qualified_table_name
                    , case when b.table_name_split_3 <> '' then b.table_name_split_1 else null end as table_catalog 
                    , case when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 <> '' then b.table_name_split_2 
                            when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 = '' then b.table_name_split_1 
                            else null end as table_schema 
                    from 
                        (select a.query_id, a.user_name, a.start_time, a.query_text, a.clean_query_text, a.table_name, a.from_counter, a.join_counter
                        , split_part(a.table_name, '.', 0) as table_name_split_0
                        , split_part(a.table_name, '.', 1) as table_name_split_1
                        , split_part(a.table_name, '.', 2) as table_name_split_2
                        , split_part(a.table_name, '.', 3) as table_name_split_3
                        from 
                            (select query_id, user_name, start_time, query_text, clean_query_text, REGEXP_SUBSTR(clean_query_text, ' from ([^\\\\ ]*)', 1, `+v_from_counter+`, 'ie', 1) as table_name, `+v_from_counter+` as from_counter, 0 as join_counter from tmp_query_hist_clean) a ) b) c
                        where c.qualified_table_name in (select table_name from tmp_tbl_vw_list group by table_name) and c.qualified_table_name <> '' group by c.query_id, c.user_name, c.start_time, c.query_text, c.clean_query_text, c.table_name, c.table_name_split_0, c.table_name_split_1, c.table_name_split_2, c.table_name_split_3, c.qualified_table_name, c.table_catalog, c.table_schema, c.from_counter, c.join_counter;`;

                    try{
                        processes=processes+"call get_from_loop_table_name_values_sql to insert table name data - query_id: "+v_q_id+"; from_count: "+v_from_count+"; join_count: "+v_join_count+"; v_from_counter: "+v_from_counter+"; while_counter: "+while_counter+" / "
                        stmt = snowflake.createStatement(
                        {sqlText: get_from_loop_table_name_values_sql}); 
                        from_loop_table_name_values_result = stmt.execute();
                        from_loop_table_name_values_result.next();
                        }

                    catch (err)  {
                        flg_build_sql="fail";
                        status="F";
                        errmsg_build_sql =  "Failed: Code: " + err.code + "\\n  State: " + err.state;
                        errmsg_build_sql += "\\n  Message: " + err.message;
                        errmsg_build_sql += "\\nStack Trace:\\n" + err.stackTraceTxt; 
                        processes=processes+" ERROR: call get_table_name_values_sql to insert table name data - "+v_q_id+" / - "+errmsg_build_sql+" / "
                    }   
                    v_from_counter++;
                } // from_count loop 
                if (v_join_count > 0) {
                    v_join_counter = 1;
//                  for (let v_join_counter=1; v_join_counter < v_join_count+1; v_join_counter++) {
                    while (v_join_counter < v_join_count+1) {
                            get_join_loop_table_name_values_sql=`insert into tmp_tbl_query_hist_loops (query_id, user_name, start_time, query_text, clean_query_text, table_name, table_name_split_0, table_name_split_1, table_name_split_2, table_name_split_3, qualified_table_name, table_catalog, table_schema, from_count, from_counter, join_count, join_counter) 
                            select c.query_id, c.user_name, c.start_time, c.query_text, c.clean_query_text, c.table_name, c.table_name_split_0, c.table_name_split_1, c.table_name_split_2, c.table_name_split_3, c.qualified_table_name, c.table_catalog, c.table_schema, `+v_from_count+` as from_count, c.from_counter, `+v_join_count+` as join_count, c.join_counter
                            from 
                            (select b.query_id, b.user_name, b.start_time, b.query_text, b.clean_query_text, b.table_name, b.from_counter, b.join_counter
                            , b.table_name_split_0, b.table_name_split_1, b.table_name_split_2, b.table_name_split_3
                            , case when b.table_name_split_3 <> '' then b.table_name_split_3 
                                    when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 = '' 
                                    then b.table_name_split_2 
                                    else b.table_name end as qualified_table_name
                            , case when b.table_name_split_3 <> '' then b.table_name_split_1 else null end as table_catalog 
                            , case when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 <> '' then b.table_name_split_2 
                                    when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 = '' then b.table_name_split_1 
                                    else null end as table_schema 
                            from 
                            
                            (select a.query_id, a.user_name, a.start_time, a.query_text, a.clean_query_text, a.table_name, a.from_counter, a.join_counter
                            , split_part(a.table_name, '.', 0) as table_name_split_0
                            , split_part(a.table_name, '.', 1) as table_name_split_1
                            , split_part(a.table_name, '.', 2) as table_name_split_2
                            , split_part(a.table_name, '.', 3) as table_name_split_3
                            from 
                            (select query_id, user_name, start_time, query_text, clean_query_text, REGEXP_SUBSTR(clean_query_text, ' join ([^\\\\ ]*)', 1, `+v_join_counter+`, 'ie', 1) as table_name, 0 as from_counter, `+v_join_counter+` as join_counter from tmp_query_hist_clean) a ) b) c
                            
                            where c.qualified_table_name in (select table_name from tmp_tbl_vw_list group by table_name) and c.qualified_table_name <> '' group by c.query_id, c.user_name, c.start_time, c.query_text, c.clean_query_text, c.table_name, c.table_name_split_0, c.table_name_split_1, c.table_name_split_2, c.table_name_split_3, c.qualified_table_name, c.table_catalog, c.table_schema, c.from_counter, c.join_counter;`;

                        try{
                            processes=processes+"call get_join_loop_table_name_values_sql to insert table name data - "+v_q_id+"; join_count: "+v_join_count+"; v_join_counter: "+v_join_counter+"; while_counter: "+while_counter+" / "
                            stmt = snowflake.createStatement(
                            {sqlText: get_join_loop_table_name_values_sql}); 
                            join_loop_table_name_values_result = stmt.execute();
                            join_loop_table_name_values_result.next();
                            }

                        catch (err)  {
                            flg_build_sql="fail";
                            status="F";
                            errmsg_build_sql =  "Failed: Code: " + err.code + "\\n  State: " + err.state;
                            errmsg_build_sql += "\\n  Message: " + err.message;
                            errmsg_build_sql += "\\nStack Trace:\\n" + err.stackTraceTxt; 
                            processes=processes+" ERROR: call get_join_loop_table_name_values_sql to insert table name data - "+v_q_id+" / - "+errmsg_build_sql+" / "
                        }   
                        v_join_counter++;
                    } // join_count loop
                } // join_count > 0
            } // v_from_count > 0
            else {processes=processes+"No tables present / "}
            processes = processes+" while_counter: "+while_counter+" / ";
        } // while statement

Here's an example of a result I'm seeing:

Via Active questions tagged javascript - Stack Overflow https://ift.tt/BpEImlW