Hey there,
I have a Snowflake Stored procedure that creates few temporary tables. Find below code for reference:
CREATE OR REPLACE PROCEDURE create_temp_tables()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
-- Create temporary table 1
CREATE TEMPORARY TABLE temp_table1_tst AS
SELECT TOP 100 * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
-- Create temporary table 2
CREATE TEMPORARY TABLE temp_table2_tst AS
SELECT TOP 150 * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
-- Create temporary table 3
CREATE TEMPORARY TABLE temp_table3_tst AS
SELECT TOP 300 * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
-- Create temporary table 4
CREATE TEMPORARY TABLE temp_table4_tst AS
SELECT TOP 450 * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
RETURN 'Temporary tables created successfully';
END;
$$;
In my workflow, I execute the stored procedure. since I can't execute multiple SQL queries in same resource query component I used another Snowflake component to fetch data from temporary table. But it is behaving oddly. When i run it for 1st time I get error that table doesn't exist and if I run twice I get data. Also, out of 4 tables, for 2 tables I get data and for 2 tables I don't.
Find below snippet for reference -
Any idea why this is happening ?? Is it a bug from Retool side ?? wanted to check If this can be solved because in actual use case I can't create physical tables and I need temporary tables only!!