Snowflake stored procedure error 'Invalid data type [VARCHAR(5)] for predicate [?] '

Question

I am running this stored procedure on Snowflake but I am getting this error ""Execution error in store procedure READ_RESULT_SET: SQL compilation error: Invalid data type [VARCHAR(5)] for predicate [?] At Statement.execute, line 12 position 19""

Tables info: Table1.header = [Segment, Condition], Table1.column1 = [aaa,bbb,ccc,ddd], Table1.column2 = [ A=1, B=1, C=1, D=1]

Table2.header = [A, B, C, D], Table2.column1 = [1,0,0,0], Table2.column2 = [1,0,1,0], Table2.column3 = [0,0,0,0], Table2.column4 = [0,1,0,0]

I am trying to get column2 from Table1 and run those as my condition in the second query. if I copy the same conditions and add them manually to the query, it works perfectly.

create or replace procedure read_result_set()
returns float not null
language javascript
as     
$$  
var my_sql_command = "select * from TABLE1";
var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
var result_set1 = statement1.execute();
// Loop through the results, processing one row at a time... 
var result = 0
while (result_set1.next())  {
    var condition = result_set1.getColumnValue(2);

    stmt = snowflake.createStatement( { sqlText: "select * from TABLE2 where ?;",binds:[condition] } );   ###THE PROBLEM IS IN THIS LINE
    //stmt = snowflake.createStatement( { sqlText: "select * from TABLE2 where A = 1;" } );   ####If I try this line instead, it works perfect
    res = stmt.execute();

    while (res.next())  {
      var one = res.getColumnValue(1);
      var two = res.getColumnValue(2);
      var three = res.getColumnValue(3);
      var four = res.getColumnValue(4);
      stmt2 = snowflake.createStatement( { sqlText: "INSERT INTO RESULT  VALUES (?, ?, ?, ?);", binds:[one, two, three, four, one] } );
      res2 = stmt2.execute();
      }
    //res.next();
    //returned_value = res.getColumnValue(1);
   }
 return 0.0; // Replace with something more useful.
 $$
 ;

 call READ_RESULT_SET()

Chris Chris · Accepted Answer · 2020-01-09T05:39:45

Your binding is not being set as an expression but rather as a string.

You're expecting WHERE A = 1
What you're getting WHERE 'A = 1'

You could try using concatenation instead of binding, changing the data in table1, or parsing out just the value from column2 using a substring or regex pattern matching function.

Snowflake stored procedure error 'Invalid data type [VARCHAR(5)] for predicate [?] '

1 Answers