CODEWITHSUNDEEP
snowflake-cloud-data-platform
Mozhi
Mozhi

Reputation: 827

How to replace loop results inside snowflake.execute statement?

I am looking for a way to iterate over result set that has columns id1, id2 and pass the resultset column values to be used in inner query as shown below.

Variables of resultset

Eg:

select * from months cross join (select column1 AS "id1",column2 AS "id2")
Select "GEOGRAPHY" from mytable WHERE id1 = column1 and id2 = column2

However when i execute the stored procedure , i encounter following error. Any pointers much appreciated . Thank you

Error:

Execution error in stored procedure TEST_PROC_STMT: SQL compilation error: error line 8 at position 81 invalid identifier 'COLUMN2' At Snowflake.execute, line 14 position 10

Full Query

create or replace procedure TEST_PROC_STMT()
    returns varchar not null
    language javascript
    EXECUTE AS CALLER
as
$$
 
 var distinct_sql_command = "select distinct id1, id2 from mytable ";
    var statement1 = snowflake.createStatement( {sqlText: distinct_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValueAsString(1);
       var column2 = result_set1.getColumnValueAsString(2);
       
snowflake.execute( {sqlText: `

INSERT INTO mytable("GEOGRAPHY")(
    Select  * from (
             with months as (
                 select dateadd(month, seq4(), '2020-02-01') "REPORTING MONTH" from table (generator(rowcount => 12))
             ), months_ids as (
                 select * from months cross join (select column1 AS "id1",column2 AS "id2")
             ) ,
                event_months as (
                      Select * from (Select *,ROW_NUMBER() OVER (PARTITION BY id1,id2 ORDER BY "REPORTING MONTH") As rn FROM mytable) Where rn =1
                  ) ,
                  final as (
                      select "REPORTING MONTH",id1,id2
                           , (select array_agg("GEOGRAPHY") within group (order by "REPORTING MONTH" desc)  from mytable where a."REPORTING MONTH">="REPORTING MONTH" and a.id1=id1

                      from months_ids a order by "REPORTING MONTH"
                  )
             Select a."GEOGRAPHY" from final a left join event_months b on  a.id1=b.id1 and a.id2 = b.id2 where a."REPORTING MONTH" > b."REPORTING MONTH"
                 Except
             Select "GEOGRAPHY" from mytable WHERE id1 = column1 and id2 = column2
         ) 
)
`});

}

return "success";

$$
;

CALL TEST_PROC_STMT();

Upvotes: 1

Views: 776

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11086

There are at least two problems with the code. First, "var" defines a variable, which is a one-time operation. In this case it needs to be moved out of the while loop:

// Loop through the results, processing one row at a time... 
while (result_set1.next())  {
   var column1 = result_set1.getColumnValueAsString(1);
   var column2 = result_set1.getColumnValueAsString(2);

This is an easy fix:

// Loop through the results, processing one row at a time... 
var column1;
var column2;
while (result_set1.next())  {
   column1 = result_set1.getColumnValueAsString(1);
   column2 = result_set1.getColumnValueAsString(2);

The other problem is the code specifies "column2" as a literal column name in the SQL, not a variable. You can tell this because the error message uppercased the variable name, so Snowflake is looking for "COLUMN2" and can't find it. There's also a use of "column1" that appears it should be a replacement variable.

You can fix that by making it a replacement variable. Change these two lines:

select * from months cross join (select column1 AS "id1",column2 AS "id2")

... and this one ...

Select "GEOGRAPHY" from mytable WHERE id1 = column1 and id2 = column2

To this:

select * from months cross join (select ${column1} AS "id1", ${column2} AS "id2")

... and this ...

Select "GEOGRAPHY" from mytable WHERE id1 = column1 and id2 = ${column2}

Note that the ${replacement_variable} syntax only works in JavaScript when you define a string using backticks. It will not work when using single or double quotes to terminate a string.

Getting past those two may expose others, but could make it just run.

Upvotes: 1

Related Questions