I'm trying to join the table resultset returned from a stored procedure to a table in snowflake.
The methods that I tried :
Method #1:
CREATE TEMPORARY TABLE temp_result AS (
SELECT *
FROM TABLE(execute_sql_from_sp())
);
Method #2:
WITH sp_result AS (
SELECT *
FROM TABLE(execute_sql_from_table())
)
SELECT t.column1, t.column2, sp_result.column3
FROM your_table t
JOIN sp_result ON t.id = sp_result.id;
None of these work. Any ideas on how we can achieve this?
As per the doc's Stored Procedure's results are not inline available:
So you have to do it indirectly.
CREATE OR REPLACE procedure FIRST_PROC()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
A = 'DONE';
return A;
$$;
call first_proc();
select * from table(result_scan(last_query_id()));