Following up from this thread
with a as (
select * from hubspot.information_schema.tables
where table_catalog = 'HUBSPOT' AND TABLE_SCHEMA = 'MONGODB' and table_name != '_SDC_REJECTED' and table_type = 'BASE TABLE'
),
b as (
select * ,
$$SELECT * FROM HUBSPOT.MONGODB.TABLE_NAME$$ t,
replace(t,'TABLE_NAME',table_name) as sql
from a
)
, test as (
select listagg(sql,'\nUNION ALL\n') within group (order by table_schema, table_catalog)
from b
)
EXECUTE IMMEDIATE SELECT * FROM test
;
I have managed to create a select statement which unions a lit of tables from a specific schema. How do I then execute this string?
I've tried EXECUTE IMMEDIATE but I keep getting this error: 'Syntax error: unexpected 'EXECUTE'. (line 20)'
CodePudding user response:
Snowflake script block could be used:
DECLARE
res RESULTSET;
sqlQuery TEXT;
BEGIN
WITH a as (
select *
from hubspot.information_schema.tables
where table_catalog = 'HUBSPOT'
AND TABLE_SCHEMA = 'MONGODB'
and table_name != '_SDC_REJECTED'
and table_type = 'BASE TABLE'
),b as (
select * ,
$$SELECT * FROM HUBSPOT.MONGODB.TABLE_NAME$$ t,
replace(t,'TABLE_NAME',table_name) as sql
from a
), test as (
select listagg(sql,'\nUNION ALL\n')
within group (order by table_schema, table_catalog) AS query
from b
)
SELECT query
INTO :sqlQuery
FROM test;
res := (EXECUTE IMMEDIATE :sqlQuery);
RETURN TABLE(res);
END;
Related: SELECT ... INTO ... and EXECUTE IMMEDIATE
Another option is session variable(though the length of string is limited to 256 bytes):
SET sqlQuery = (SELECT ... );
-- SELECT query should return single column/row
EXECUTE IMMEDIATE $sqlQuery;