I need to execute a select query from Snowflake UDF and assign to a variable.. Since am using variable i guess i can only use JAVASCRIPT UDF in snowflake like below..
But the select query inside Snowflake JAVASCRIPT UDF is returning NULL everytime...
Please advise..
create or replace function fun1()
returns float
language JAVASCRIPT
as
$$
var a
a=select * from emp where salary like '%100%' and empno=1
return a
$$
Assuming below: You want to retrun salary of employee whose empno=1, you can write something like below:
create or replace procedure Fun1()
returns RETURNS TABLE (id int, name varchar)
language javascript
as
$$
var sql_command = "select salary from emp where salary like '%100%' and empno=1'";
var result;
var stmt = snowflake.createStatement( {sqlText: sql_command} );
var resultSet = stmt.execute();
result=resultSet.next();
return result;
$$;If this is not the case, then you have to write TableUDf's to return the table data. You can use SQL Scripting as well.